Rapporto Dashboard - Utente-a-Utente

Dati e reportistica
, ,
1

Questa è una versione SQL del Report della Bacheca per Utente-a-Utente (con risposte) e Utente-a-Utente (escluse le risposte).

Questo report conta il numero di messaggi personali inviati dagli utenti ogni giorno, all’interno di un intervallo di date specificato, fornendo informazioni sull’attività di coinvolgimento degli utenti nella comunicazione privata.

--[params]
-- date :start_date = 2024-01-06
-- date :end_date = 2024-02-07
-- boolean :include_replies = false

SELECT 
    p.created_at::date as day,
    COUNT(p.user_id) as personal_messages
FROM posts p
INNER JOIN topics t ON (p.topic_id = t.id)
WHERE archetype = 'private_message'
    AND p.user_id > 0
    AND t.subtype = 'user_to_user'
    AND p.deleted_at ISNULL
    AND t.deleted_at ISNULL
    AND p.created_at::date BETWEEN :start_date AND :end_date
    AND (:include_replies = true OR p.post_number = 1)
GROUP BY p.created_at::date
ORDER BY p.created_at::date

Spiegazione della Query SQL

Questa query SQL esegue le seguenti azioni:

  • Parametri:
    • :start_date e :end_date, che vengono utilizzati per filtrare i dati all’interno dell’intervallo di date specificato. Entrambi i parametri di data accettano il formato AAAA-MM-GG.
    • :include_replies per determinare se le risposte ai messaggi iniziali debbano essere incluse nel conteggio. Impostato su false per impostazione predefinita per rispecchiare il comportamento del report Utente-a-Utente (escluse le risposte). L’impostazione di questo parametro su true rispecchierà il comportamento del report Utente-a-Utente (con risposte).
  • Selezione dei Dati:
    • La query seleziona la data created_at dalla tabella posts, che rappresenta la data in cui è stato creato ciascun messaggio.
  • Operazione di Join:
    • La query esegue un INNER JOIN tra le tabelle posts e topics sui loro campi id. Questo join è cruciale in quanto collega ogni post al suo argomento corrispondente, permettendoci di filtrare solo i post che fanno parte di una conversazione di messaggi personali.
  • Criteri di Filtraggio: La query applica diversi filtri:
    • Considera solo i post che fanno parte di un argomento con un archetype di ‘private_message’, assicurando che vengano contati solo i messaggi personali.
    • Esclude qualsiasi post non associato a un utente assicurando che user_id sia maggiore di 0.
    • Restringe ulteriormente i risultati agli argomenti con un subtype di ‘user_to_user’, che indica un messaggio personale tra singoli utenti.
    • Esclude eventuali post o argomenti che sono stati eliminati verificando che deleted_at sia NULL per entrambe le tabelle.
    • Applica il filtro dell’intervallo di date per includere solo i post creati tra :start_date e :end_date.
    • Se :include_replies è false, viene contato solo il primo post in ogni argomento (p.post_number = 1), escludendo le risposte.
  • Aggregazione: La query raggruppa i risultati per la data in cui sono stati creati i post, consentendo di contare il numero di messaggi inviati ogni giorno.
  • Ordinamento: Infine, la query ordina i risultati per data in ordine crescente, fornendo una sequenza cronologica dell’attività dei messaggi personali da utente a utente.

Esempio di Risultati

day personal_messages
2023-11-11 92
2023-11-12 57
2023-11-13 345
2023-11-14 124
2023-11-15 56
4 Mi Piace