Rapporto Dashboard - Mi piace

Comunità Dati e reportistica
, ,
4

Il mio forum non è molto grande e la maggior parte delle nostre reazioni “mi piace” proviene dallo “staff” (amministratori, moderatori, TL=4). Volevo vedere come si confrontano i “mi piace” degli utenti normali rispetto allo “staff” e elencare il numero di post al giorno per avere un’idea migliore di cosa sta succedendo e dove dobbiamo concentrare gli sforzi per migliorare l’uso delle reazioni.

Io e il mio amico ChatGPT abbiamo ideato questo:

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-12-31

WITH date_range AS (
  SELECT date_trunc('day', series) AS date
  FROM generate_series(
    :start_date::timestamp,
    :end_date::timestamp,
    '1 day'::interval
  ) series
),
staff_users AS (
  SELECT id
  FROM users
  WHERE admin = true OR moderator = true OR trust_level = 4
)

SELECT
  dr.date::date,
  COALESCE(pa_non_staff.regular_likes_count, 0) AS regular_likes_count,
  COALESCE(pa_staff.staff_likes_count, 0) AS staff_likes_count,
  COALESCE(pa_non_staff.regular_likes_count, 0) + COALESCE(pa_staff.staff_likes_count, 0) AS total_likes,
  COALESCE(posts_count.posts_per_day, 0) AS posts_per_day
FROM
  date_range dr
LEFT JOIN (
  SELECT
    date_trunc('day', pa.created_at) AS action_date,
    COUNT(*) AS regular_likes_count
  FROM post_actions pa
  WHERE pa.post_action_type_id = 2
    AND pa.user_id NOT IN (SELECT id FROM staff_users)
    AND pa.created_at >= :start_date
    AND pa.created_at <= (:end_date::date + 1)
  GROUP BY action_date
) pa_non_staff ON dr.date = pa_non_staff.action_date
LEFT JOIN (
  SELECT
    date_trunc('day', pa.created_at) AS action_date,
    COUNT(*) AS staff_likes_count
  FROM post_actions pa
  WHERE pa.post_action_type_id = 2
    AND pa.user_id IN (SELECT id FROM staff_users)
    AND pa.created_at >= :start_date
    AND pa.created_at <= (:end_date::date + 1)
  GROUP BY action_date
) pa_staff ON dr.date = pa_staff.action_date
LEFT JOIN (
  SELECT
    date_trunc('day', p.created_at) AS post_date,
    COUNT(*) AS posts_per_day
  FROM posts p
  WHERE p.created_at >= :start_date
    AND p.created_at <= (:end_date::date + 1)
  GROUP BY post_date
) posts_count ON dr.date = posts_count.post_date
ORDER BY dr.date

Modifiche alla query originale di @SaraDev (grazie, Sara!):
Riepilogo modifiche SQL

  1. Genera Gruppo Staff:
    Aggiunta una CTE staff_users per identificare gli utenti staff dalla tabella users. Un utente staff è definito come uno qualsiasi dei seguenti:
  • admin = true
  • moderator = true
  • trust_level = 4
  1. Separa Mi Piace Staff:
    Aggiunta una sottoquery per calcolare il conteggio dei mi piace dagli utenti staff (staff_likes_count) filtrando post_actions per user_id nel gruppo staff_users.
  2. Rinomina Colonna Mi Piace Non Staff:
    Modificata l’etichetta di output per i mi piace non staff da likes_count a regular_likes_count.
  3. Aggiungi Mi Piace Totali:
    Introdotta una colonna total_likes per sommare regular_likes_count e staff_likes_count.
  4. Aggiungi Post al Giorno:
    Aggiunta una sottoquery per calcolare il numero di post al giorno (posts_per_day) e unita all’intervallo di date.
    (Sì, ChatGPT ha creato anche questo elenco di modifiche per me.)

Esempio di risultati:
|data|regular_likes_count|staff_likes_count|posts_per_day|
|:—:|:—:|:—:|:—:expressionless:
|1/1/24|0|6|7|
|1/2/24|0|5|3|
|1/3/24|1|0|4|
|1/4/24|1|2|5|
|1/5/24|9|9|30|
|1/6/24|0|1|11|
|1/7/24|2|4|11|
|1/8/24|0|5|18|
|1/9/24|0|0|2|
|1/10/24|0|0|7|
|1/11/24|0|4|5|
|1/12/24|4|0|4|
|1/13/24|6|0|10|
|1/14/24|1|7|18|
|1/15/24|2|4|7|\

Stessa query riportata per settimane per smussare 
-- [params]
-- integer :weeks_ago = 52

WITH date_range AS (
  SELECT date_trunc('week', series) AS week_start
  FROM generate_series(
    date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval,
    date_trunc('week', now()),
    '1 week'::interval
  ) series
),
staff_users AS (
  SELECT id
  FROM users
  WHERE admin = true OR moderator = true OR trust_level = 4
)

SELECT
  dr.week_start::date AS week_start,
  COALESCE(pa_non_staff.regular_likes_count, 0) AS regular_likes_count,
  COALESCE(pa_staff.staff_likes_count, 0) AS staff_likes_count,
  COALESCE(pa_non_staff.regular_likes_count, 0) + COALESCE(pa_staff.staff_likes_count, 0) AS total_likes,
  COALESCE(posts_count.posts_per_week, 0) AS posts_per_week
FROM
  date_range dr
LEFT JOIN (
  SELECT
    date_trunc('week', pa.created_at) AS action_week,
    COUNT(*) AS regular_likes_count
  FROM post_actions pa
  WHERE pa.post_action_type_id = 2
    AND pa.user_id NOT IN (SELECT id FROM staff_users)
    AND pa.created_at >= date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval
    AND pa.created_at <= date_trunc('week', now())
  GROUP BY action_week
) pa_non_staff ON dr.week_start = pa_non_staff.action_week
LEFT JOIN (
  SELECT
    date_trunc('week', pa.created_at) AS action_week,
    COUNT(*) AS staff_likes_count
  FROM post_actions pa
  WHERE pa.post_action_type_id = 2
    AND pa.user_id IN (SELECT id FROM staff_users)
    AND pa.created_at >= date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval
    AND pa.created_at <= date_trunc('week', now())
  GROUP BY action_week
) pa_staff ON dr.week_start = pa_staff.action_week
LEFT JOIN (
  SELECT
    date_trunc('week', p.created_at) AS post_week,
    COUNT(*) AS posts_per_week
  FROM posts p
  WHERE p.created_at >= date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval
    AND p.created_at <= date_trunc('week', now())
  GROUP BY post_week
) posts_count ON dr.week_start = posts_count.post_week
ORDER BY dr.week_start
Nel caso fosse interessante, ecco i prompt finali che hanno modificato la query di Sara:

Ho una query SQL che riporta il conteggio giornaliero dei mi piace (likes_count) tra due date, ma devo apportare i seguenti miglioramenti per produrre un output finale che aggreghi i dati per settimane e includa dettagli aggiuntivi:

  1. Definire un Gruppo Staff:
  • Creare un gruppo staff_users dalla tabella users. Un utente dovrebbe essere considerato staff se soddisfa uno dei seguenti criteri:
    • admin = true
    • moderator = true
    • trust_level = 4
  1. Separare i Mi Piace per Staff e Non Staff:
  • Aggiungere due colonne separate:
    • regular_likes_count: Conteggio dei mi piace dagli utenti non staff.
    • staff_likes_count: Conteggio dei mi piace dagli utenti staff.
  • Assicurarsi che la colonna regular_likes_count escluda i mi piace generati dagli utenti staff.
  1. Aggiungere Mi Piace Totali:
  • Includere una colonna total_likes che somma regular_likes_count e staff_likes_count.
  1. Aggiungere Post per Periodo:
  • Aggiungere una colonna posts_per_week che conta il numero di post creati durante ogni settimana.
  1. Aggregare per Settimane:
  • Modificare la query per raggruppare tutti i dati per intervalli settimanali invece che giornalieri.
  • Includere una colonna week_start che rappresenta la data di inizio di ogni settimana.
  1. Limitare per Settimane Fa:
  • Introdurre un parametro :weeks_ago per limitare i risultati alle ultime N settimane. Il valore predefinito dovrebbe essere 52 settimane (1 anno).
  1. Ordinamento e Colonne Finali:
  • Assicurarsi che l’output sia ordinato per week_start e includa le seguenti colonne in quest’ordine:
    1. week_start: La data di inizio della settimana.
    2. regular_likes_count: Il conteggio dei mi piace dagli utenti non staff.
    3. staff_likes_count: Il conteggio dei mi piace dagli utenti staff.
    4. total_likes: La somma di regular_likes_count e staff_likes_count.
    5. posts_per_week: Il numero di post creati durante la settimana.