Dashboard Report - Crescita Livello di Fiducia

Dati e reportistica
, ,
1

Questo è un report di dashboard in versione SQL per la crescita del livello di fiducia.

Questo report di dashboard conta il numero di utenti che raggiungono i livelli di fiducia 1, 2, 3 e 4 ogni giorno nell’intervallo di date specificato e aiuta gli amministratori a monitorare il coinvolgimento degli utenti e la progressione attraverso i livelli di fiducia in una community di Discourse.

-- [params]
-- date :start_date = 2024-01-01    
-- date :end_date = 2024-02-01

SELECT
  date(created_at) AS date,
  COUNT(*) FILTER (WHERE previous_value::integer < 1 AND new_value = '1') AS tl1_reached,
  COUNT(*) FILTER (WHERE previous_value::integer < 2 AND new_value = '2') AS tl2_reached,
  COUNT(*) FILTER (WHERE previous_value::integer < 3 AND new_value = '3') AS tl3_reached,
  COUNT(*) FILTER (WHERE previous_value::integer < 4 AND new_value = '4') AS tl4_reached
FROM user_histories
WHERE created_at >= :start_date
  AND created_at <= :end_date
  AND (action = 2 OR action = 15)
GROUP BY date(created_at)
ORDER BY date(created_at)

Spiegazione della query SQL

La query funziona selezionando i record dalla tabella user_histories, che registra le modifiche negli stati degli utenti, inclusi i loro livelli di fiducia.

Ecco una ripartizione della query:

  • Parametri di data: la query accetta due parametri, :start_date e :end_date, che definiscono l’intervallo di date per il report. Entrambi i parametri di data accettano il formato di data AAAA-MM-GG.
  • date(created_at) AS date: questa parte dell’istruzione SELECT estrae la data dal timestamp created_at e la etichetta come ‘date’ per ogni record. Questo verrà utilizzato per raggruppare i risultati per giorno.
  • COUNT(*) FILTER (WHERE previous_value::integer < X AND new_value = 'Y') AS tlY_reached: questa sezione conta il numero di utenti che sono progrediti a un livello di fiducia specifico (Y) da un livello di fiducia inferiore (inferiore a X). La clausola FILTER viene utilizzata per includere solo le righe che corrispondono alla condizione specificata. Il cast ::integer viene utilizzato per convertire previous_value in un intero per il confronto. Ci sono quattro di queste colonne di conteggio, una per ogni livello di fiducia da 1 a 4.
  • FROM user_histories: questo specifica la tabella da cui recuperare i dati.
  • WHERE created_at >= :start_date AND created_at <= :end_date: questo filtra i record per includere solo quelli all’interno dell’intervallo di date specificato.
  • AND (action = 2 OR action = 15): questo filtra i record per includere solo quelli in cui l’azione indica una modifica del livello di fiducia. Le azioni specifiche che corrispondono alle modifiche del livello di fiducia sono rappresentate dai numeri 2 e 15.
  • GROUP BY date(created_at): questo raggruppa i risultati per data, in modo da ottenere un conteggio delle modifiche del livello di fiducia per ogni giorno nell’intervallo specificato.
  • ORDER BY date(created_at): questo ordina i risultati cronologicamente per data.

Risultati di esempio

date tl1_reached tl2_reached tl3_reached tl4_reached
2024-01-01 1 3 1 0
2024-01-02 5 1 0 1
2024-01-03 8 2 3 0
… … … … …
4 Mi Piace