Relatório do Painel - Novos Contribuidores

Dados e relatórios
, ,
1

Este é um relatório de painel em SQL para novos contribuidores.

Este relatório de painel SQL foi projetado para fornecer aos administradores uma visão clara do engajamento do usuário em seu fórum Discourse, identificando quantos usuários fizeram sua primeira postagem em cada dia dentro de um intervalo de datas especificado.

-- [params]
-- date :start_date = 2023-12-15
-- date :end_date = 2024-01-16

SELECT
  date_trunc('day', p.created_at)::date AS day,
  COUNT(DISTINCT p.user_id) AS new_contributors
FROM
  posts p
INNER JOIN (
  SELECT
    user_id,
    MIN(created_at) as first_post_date
  FROM
    posts
  WHERE deleted_at IS NULL
  GROUP BY
    user_id
) fp ON p.user_id = fp.user_id
WHERE
  p.created_at = fp.first_post_date
  AND p.created_at BETWEEN :start_date AND (:end_date::date + 1)
GROUP BY
  day
ORDER BY
  day

Explicação da Consulta SQL

O relatório busca a contagem de usuários distintos que fizeram sua primeira postagem em cada dia dentro do intervalo de datas especificado. Ele faz isso realizando as seguintes etapas:

Parâmetros:

  • A consulta aceita dois parâmetros, :start_date e :end_date, que definem o intervalo de datas para o relatório. Ambos os parâmetros de data aceitam o formato de data AAAA-MM-DD.

Subconsulta: Determinando a Data da Primeira Postagem para Cada Usuário

Este relatório contém uma subconsulta que seleciona duas colunas da tabela posts: user_id e o timestamp created_at mais antigo (apelidado de first_post_date). O timestamp created_at mais antigo representa a primeira postagem do usuário. Esta subconsulta inclui uma condição WHERE que considera apenas as postagens que não foram excluídas (deleted_at IS NULL). Finalmente, ele agrupa os resultados por user_id para garantir que estamos olhando apenas para a primeira postagem de cada usuário.

Consulta Principal: Contagem de Usuários que Fizeram Sua Primeira Postagem

A consulta principal realiza as seguintes operações:

  • JOIN: A tabela principal posts (apelidada de p) é unida ao resultado da subconsulta (apelidado de fp) em user_id para corresponder cada postagem com a primeira postagem do usuário correspondente.
  • Filtragem por Data: A cláusula WHERE inclui duas condições - ela compara o timestamp created_at de cada postagem com o first_post_date da subconsulta para garantir que estamos lidando apenas com as primeiras postagens, e verifica se o timestamp created_at está dentro do intervalo de datas especificado, incluindo o end_date (+1 dia para incluir o dia final completamente).
  • Agregação: As postagens são então agrupadas pela data, truncadas para o dia sem componente de tempo (date_trunc('day', p.created_at)::date), o que permite contar os usuários únicos que postaram pela primeira vez em cada dia.
  • Contagem: Usando COUNT(DISTINCT p.user_id), obtemos o número de usuários únicos que fizeram sua primeira postagem em cada dia respectivo.
  • Ordenação: Os resultados são ordenados por dia em ordem crescente (ORDER BY day) para fornecer uma visão cronológica do engajamento do usuário.

Saída Final

O relatório final consiste em duas colunas:

  • day: A data de engajamento do usuário sem o componente de tempo.
  • new_contributors: O número de usuários distintos que fizeram sua primeira postagem no fórum para cada dia.

Resultados de Exemplo

day new_contributors
2023-12-15 16
2023-12-16 8
2023-12-17 7
2023-12-18 19
2023-12-19 15
2 curtidas
Admin dashboard report reference guide