Lettori e Utenti attivi al giorno

Dati e reportistica
1

Questo report SQL fornisce un conteggio giornaliero di lettori (utenti che leggono solo post) e utenti attivi (utenti che hanno creato almeno un post durante il giorno), in un intervallo di date specificato, con un filtro opzionale per l’ID della categoria.

Questo report è progettato per aiutare gli amministratori a comprendere quanto è attiva la loro community, non solo in termini di creazione di contenuti ma anche di consumo di contenuti. Questa doppia prospettiva consente una comprensione più dettagliata del coinvolgimento degli utenti, evidenziando i giorni con elevata attività di lettura o di pubblicazione.

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-02-01
-- int :reader_post_read_count = 10
-- null int :category_id

WITH posts_by_user AS (
    SELECT
        p.user_id,
        p.created_at::date AS post_date,
        p.topic_id,
        COUNT(*) AS posts
    FROM posts p
    WHERE p.created_at::date BETWEEN :start_date AND :end_date
    GROUP BY p.user_id, post_date, p.topic_id
), 

posts_read_by_user AS (
    SELECT
        uv.user_id,
        uv.visited_at::date AS visit_date,
        SUM(uv.posts_read) AS posts_read
    FROM user_visits uv
    LEFT JOIN group_users gu ON gu.user_id = uv.user_id
    LEFT JOIN groups g ON g.id = gu.group_id
    LEFT JOIN posts p ON p.user_id = uv.user_id AND p.created_at BETWEEN :start_date AND :end_date
    LEFT JOIN topics t ON t.id = p.topic_id AND t.category_id = :category_id
    WHERE uv.visited_at BETWEEN :start_date AND :end_date
    AND (:category_id IS NULL OR t.category_id IS NOT NULL)
    GROUP BY uv.user_id, visit_date
),

active_users_per_day AS (
    SELECT
        pbu.post_date,
        COUNT(DISTINCT pbu.user_id) AS active_user_count
    FROM posts_by_user pbu
    JOIN topics t ON pbu.topic_id = t.id
    WHERE posts > 0
    AND (:category_id IS NULL OR t.category_id = :category_id)
    GROUP BY post_date
),

readers_per_day AS (
    SELECT
        prbu.visit_date,
        COUNT(DISTINCT prbu.user_id) AS reader_count
    FROM posts_read_by_user prbu
    LEFT JOIN posts_by_user pbu ON prbu.user_id = pbu.user_id AND prbu.visit_date = pbu.post_date
    WHERE pbu.posts IS NULL
    AND prbu.posts_read > :reader_post_read_count
    GROUP BY prbu.visit_date
)

SELECT
    rpd.visit_date,
    rpd.reader_count,
    COALESCE(aupd.active_user_count, 0) AS active_user_count
FROM readers_per_day rpd
LEFT JOIN active_users_per_day aupd ON rpd.visit_date = aupd.post_date
ORDER BY rpd.visit_date

Spiegazione della query SQL

Parametri

  • start_date e end_date: Definiscono l’intervallo di tempo per l’analisi.
  • reader_post_read_count: Imposta la soglia per considerare qualcuno un “lettore” in base al numero di post letti.
  • category_id: Opzionale. Se specificato, il report si concentra su una singola categoria, consentendo un’analisi più mirata.

CTE

  1. posts_by_user: Questa CTE identifica il numero di post creati da ciascun utente ogni giorno nell’intervallo di date specificato. È fondamentale per comprendere quali utenti contribuiscono con contenuti e come questa attività è distribuita nel tempo.
  2. posts_read_by_user: Questa CTE calcola il numero di post letti da ciascun utente ogni giorno, sempre nell’intervallo di date specificato. Sfrutta i dati di visita degli utenti per stimare il consumo di contenuti, un indicatore chiave di coinvolgimento.
  3. active_users_per_day: Basata sulla CTE posts_by_user, questa CTE conta il numero di utenti unici che hanno pubblicato almeno una volta al giorno. Fornisce uno snapshot giornaliero dei creatori di contenuti attivi.
  4. readers_per_day: Utilizzando la CTE posts_read_by_user, questa CTE identifica gli utenti che hanno letto più di un numero specificato di post (reader_post_read_count) senza pubblicare essi stessi. Evidenzia la maggioranza silenziosa che interagisce leggendo.

Risultati

L’output finale presenta una panoramica giornaliera del coinvolgimento degli utenti, dettagliando il numero di lettori e utenti attivi (coloro che hanno pubblicato) ogni giorno.

Risultati di esempio

visit_date reader_count active_user_count
2024-01-02 150 25
2024-01-03 175 30
2024-01-04 160 20
2024-01-05 180 22
… … …
4 Mi Piace