Report Dashboard - Nuovi Contributori

Dati e reportistica
, ,
1

Questo è un report di dashboard in versione SQL per i nuovi collaboratori.

Questo report di dashboard SQL è progettato per fornire agli amministratori una chiara visione d’insieme dell’interazione degli utenti sul loro forum Discourse identificando quanti utenti hanno effettuato il loro primo post ogni giorno all’interno di un intervallo di date specificato.

-- [params]
-- date :start_date = 2023-12-15
-- date :end_date = 2024-01-16

SELECT
  date_trunc('day', p.created_at)::date AS day,
  COUNT(DISTINCT p.user_id) AS new_contributors
FROM
  posts p
INNER JOIN (
  SELECT
    user_id,
    MIN(created_at) as first_post_date
  FROM
    posts
  WHERE deleted_at IS NULL
  GROUP BY
    user_id
) fp ON p.user_id = fp.user_id
WHERE
  p.created_at = fp.first_post_date
  AND p.created_at BETWEEN :start_date AND (:end_date::date + 1)
GROUP BY
  day
ORDER BY
  day

Spiegazione della query SQL

Il report recupera il conteggio degli utenti distinti che hanno effettuato il loro primo post ogni giorno nell’intervallo di date specificato. Lo fa eseguendo i seguenti passaggi:

Parametri:

  • 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 data AAAA-MM-GG.

Query interna: Determinazione della data del primo post per ciascun utente

Questo report contiene una sottoquery che seleziona due colonne dalla tabella posts: user_id e il timestamp created_at più vecchio (alias first_post_date). Il timestamp created_at più vecchio rappresenta il primo post dell’utente. Questa sottoquery include una condizione WHERE che considera solo i post che non sono stati eliminati (deleted_at IS NULL). Infine, raggruppa i risultati per user_id per garantire che stiamo considerando solo il primo post per ciascun utente.

Query principale: Conteggio degli utenti che hanno effettuato il loro primo post

La query principale esegue le seguenti operazioni:

  • JOIN: La tabella posts principale (alias p) viene unita al risultato della sottoquery (alias fp) su user_id per associare ogni post al primo post dell’utente corrispondente.
  • Filtraggio per data: La clausola WHERE include due condizioni: confronta il timestamp created_at di ogni post con first_post_date della sottoquery per garantire che stiamo trattando solo i primi post, e verifica che il timestamp created_at rientri nell’intervallo di date specificato, inclusa la end_date (+1 giorno per includere completamente il giorno finale).
  • Aggregazione: I post vengono quindi raggruppati per data, troncati al giorno senza componente oraria (date_trunc('day', p.created_at)::date), il che consente di contare gli utenti unici che hanno pubblicato per la prima volta ogni giorno.
  • Conteggio: Utilizzando COUNT(DISTINCT p.user_id), otteniamo il numero di utenti unici che hanno effettuato il loro primo post ogni giorno.
  • Ordinamento: I risultati vengono ordinati per giorno in ordine crescente (ORDER BY day) per fornire una panoramica cronologica dell’interazione degli utenti.

Output finale

Il report finale è composto da due colonne:

  • day: La data di interazione dell’utente senza la componente oraria.
  • new_contributors: Il numero di utenti distinti che hanno effettuato il loro primo post sul forum per ogni giorno.

Risultati di esempio

day new_contributors
2023-12-15 16
2023-12-16 8
2023-12-17 7
2023-12-18 19
2023-12-19 15
2 Mi Piace