Rapporto Dashboard - Segnalibri

[SaraDev (Sara Devlaeminck)]

Questa è una versione SQL del report della dashboard per i segnalibri.

Questo report della dashboard fornisce un conteggio giornaliero dei segnalibri creati entro un intervallo di date specificato.

-- [params]
-- date :start_date
-- date :end_date

WITH bookmark_counts AS (
  SELECT
    DATE(bookmarks.created_at) AS bookmark_date,
    COUNT(DISTINCT CASE WHEN bookmarkable_type = 'Topic' THEN bookmarks.id END) AS topic_bookmarks,
    COUNT(DISTINCT CASE WHEN bookmarkable_type = 'Post' THEN bookmarks.id END) AS post_bookmarks
  FROM bookmarks
  WHERE bookmarks.created_at::date BETWEEN :start_date AND :end_date
  GROUP BY bookmark_date
)
SELECT
  bookmark_date,
  topic_bookmarks + post_bookmarks AS total_bookmarks,
  topic_bookmarks,
  post_bookmarks
FROM bookmark_counts
ORDER BY bookmark_date

Spiegazione della query SQL

Parametri

Per generare il report, è necessario fornire due parametri: :start_date e :end_date. Questi parametri definiscono l’intervallo di date per il quale si desidera analizzare l’attività dei segnalibri. Entrambi i parametri di data accettano il formato data AAAA-MM-GG.

Bookmark_Counts (CTE)

Viene utilizzata una Common Table Expression (CTE) denominata bookmark_counts per organizzare i dati prima della selezione finale. La CTE esegue le seguenti azioni:

• Filtraggio per data: Filtra i segnalibri in base alla loro data di creazione per includere solo quelli creati all’interno delle date di inizio e fine fornite (:start_date e :end_date).
• Raggruppamento per data: Raggruppa i risultati in base alla data di creazione dei segnalibri, garantendo che i conteggi vengano aggregati su base giornaliera.
• Conteggio dei segnalibri: Conta separatamente il numero di segnalibri per argomenti e post, utilizzando DISTINCT per garantire che ogni segnalibro venga contato una sola volta. Ciò viene fatto utilizzando l’aggregazione condizionale con istruzioni CASE:
  • topic_bookmarks conta i segnalibri in cui bookmarkable_type è ‘Topic’.
  • post_bookmarks conta i segnalibri in cui bookmarkable_type è ‘Post’.

Query principale

Dopo che la CTE ha organizzato i dati, l’istruzione SELECT finale recupera le seguenti colonne:

1. bookmark_date: La data per cui vengono aggregati i conteggi.
2. total_bookmarks: La somma dei segnalibri di argomenti e post per ogni giorno.
3. topic_bookmarks: Il conteggio dei segnalibri per gli argomenti per ogni giorno. Es:
   
   
4. post_bookmarks: Il conteggio dei segnalibri per i post per ogni giorno. Es:
   
   

I risultati vengono quindi ordinati per bookmark_date per presentare i dati in ordine cronologico.

Risultati di esempio

bookmark_date	total_bookmarks	topic_bookmarks	post_bookmarks
2023-12-01	16	1	15
2023-12-02	4	1	3
2023-12-03	8	0	8
2023-12-04	19	9	10
2023-12-05	18	3	15
…	…	…	…