Report Dashboard - Flag

Dati e reportistica
, ,
1

Questa è una versione SQL del Report della Bacheca per i Flag

Questo report fornisce un conteggio dei post segnalati all’interno di una categoria specifica opzionale (e opzionalmente delle sue sottocategorie) in un dato intervallo di date. Queste informazioni possono essere utili ai community manager e ai moderatori per comprendere il volume dei flag sollevati in determinate parti del forum, il che può aiutare a identificare tendenze e aree che potrebbero richiedere maggiore attenzione o moderazione.

-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2024-01-01
-- null int :category_id = 0
-- boolean :include_subcategories = false

WITH RECURSIVE subcategories AS (
    SELECT id FROM categories WHERE id = :category_id
    UNION ALL
    SELECT c.id FROM categories c
    INNER JOIN subcategories sc ON c.parent_category_id = sc.id
)

SELECT
    DATE(r.created_at) AS flag_date,
    COUNT(r.id) AS flags_count
FROM reviewables r
LEFT JOIN categories c ON r.category_id = c.id
WHERE r.type = 'ReviewableFlaggedPost'
    AND r.created_at::DATE BETWEEN :start_date AND :end_date
    AND (:category_id = 0 OR (
        :include_subcategories
        AND r.category_id IN (SELECT id FROM subcategories)
        OR NOT :include_subcategories
        AND r.category_id = :category_id
    ))
GROUP BY flag_date
ORDER BY flag_date

Spiegazione della Query SQL

La query SQL è progettata per contare il numero di flag (che sono rappresentati da record ReviewableFlaggedPost nella tabella reviewables) creati in ogni data all’interno dell’intervallo di date e della categoria specificati. Utilizza una Common Table Expression (CTE) ricorsiva per includere opzionalmente le sottocategorie della categoria scelta.

Parametri

  • :start_date: L’inizio dell’intervallo di date per cui contare i flag.
  • :end_date: La fine dell’intervallo di date per cui contare i flag.
  • :category_id: L’ID della categoria per cui contare i flag. Se impostato su 0, il filtro sulla categoria non viene applicato.
  • :include_subcategories: Un valore booleano che determina se includere o meno le sottocategorie della categoria specificata nel conteggio.

Scomposizione della Query

  1. CTE Ricorsiva (subcategories):
    • Questa CTE inizia selezionando la categoria con l’ID fornito dal parametro :category_id.
    • Successivamente, seleziona ricorsivamente tutte le sottocategorie di quella categoria unendo la tabella categories con la CTE subcategories sul campo parent_category_id.
    • Il risultato è un elenco di tutte le sottocategorie (inclusa la categoria iniziale) che verranno utilizzate per il filtraggio.
  2. Query Principale:
    • La query seleziona due colonne: la data di creazione del flag (flag_date) e il conteggio dei flag (flags_count) per quella data.
    • Unisce la tabella reviewables con la tabella categories per associare ogni flag alla sua categoria.
  3. Filtri:
    • La query filtra i record di reviewables per includere solo quelli di tipo ReviewableFlaggedPost, che rappresenta i post segnalati.
    • Garantisce che la data created_at dei flag rientri nell’intervallo di date specificato (:start_date a :end_date).
    • Applica un filtro condizionale basato sui parametri :category_id e :include_subcategories:
      • Se :category_id è 0, non viene applicato alcun filtro di categoria.
      • Se :include_subcategories è true, la query include i flag della categoria iniziale e di tutte le sue sottocategorie.
      • Se :include_subcategories è false, la query include i flag solo della categoria iniziale.
  4. Raggruppamento e Ordinamento:
    • I risultati vengono raggruppati per data di creazione del flag per fornire un conteggio per giorno.
    • I risultati vengono ordinati per data per presentare i dati in una sequenza cronologica.

Esempio di Risultati

flag_date flags_count
2023-11-19 2
2023-11-22 1
2023-11-23 1
2023-11-24 2
2023-11-26 1
… …
1 Mi Piace