Leitores e Usuários Ativos Por Dia

Dados e relatórios
1

Este relatório SQL fornece uma contagem diária de leitores (usuários que apenas leem postagens) e usuários ativos (usuários que criaram pelo menos uma postagem durante o dia), em um intervalo de datas especificado, com um filtro opcional para ID de categoria.

Este relatório foi projetado para ajudar os administradores a entender o quão ativa é a comunidade deles, não apenas em termos de criação de conteúdo, mas também de consumo de conteúdo. Essa perspectiva dupla permite uma compreensão mais detalhada do engajamento do usuário, destacando dias com alta atividade de leitura ou postagem.

-- [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

Explicação da Consulta SQL

Parâmetros

  • start_date e end_date: Definem o período de tempo para a análise.
  • reader_post_read_count: Define o limite para considerar alguém um “leitor” com base no número de postagens lidas.
  • category_id: Opcional. Quando especificado, o relatório se concentra em uma única categoria, permitindo uma análise mais direcionada.

CTEs

  1. posts_by_user: Esta CTE identifica o número de postagens criadas por cada usuário em cada dia dentro do intervalo de datas especificado. É crucial para entender quais usuários estão contribuindo com conteúdo e como essa atividade é distribuída ao longo do tempo.
  2. posts_read_by_user: Esta CTE calcula o número de postagens lidas por cada usuário em cada dia, novamente dentro do intervalo de datas especificado. Ela utiliza dados de visita do usuário para estimar o consumo de conteúdo, um indicador chave de engajamento.
  3. active_users_per_day: Com base na CTE posts_by_user, esta CTE conta o número de usuários únicos que postaram pelo menos uma vez por dia. Ela fornece um instantâneo diário de criadores de conteúdo ativos.
  4. readers_per_day: Usando a CTE posts_read_by_user, esta CTE identifica usuários que leram mais do que um número especificado de postagens (reader_post_read_count) sem postar eles mesmos. Ela destaca a maioria silenciosa que se engaja lendo.

Resultados

A saída final apresenta uma visão geral diária do engajamento do usuário, detalhando o número de leitores e usuários ativos (aqueles que postaram) a cada dia.

Exemplo de Resultados

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 curtidas