Relatório do Painel - Crescimento do Nível de Confiança

Dados e relatórios
, ,
1

Este é um relatório de painel em SQL para o crescimento do nível de confiança.

Este relatório de painel conta o número de usuários que atingem os níveis de confiança 1, 2, 3 e 4 a cada dia dentro do intervalo de datas especificado e ajuda os administradores a rastrear o engajamento e a progressão dos usuários através dos níveis de confiança em uma comunidade Discourse.

-- [params]
-- date :start_date = 2024-01-01    
-- date :end_date = 2024-02-01

SELECT
  date(created_at) AS date,
  COUNT(*) FILTER (WHERE previous_value::integer < 1 AND new_value = '1') AS tl1_reached,
  COUNT(*) FILTER (WHERE previous_value::integer < 2 AND new_value = '2') AS tl2_reached,
  COUNT(*) FILTER (WHERE previous_value::integer < 3 AND new_value = '3') AS tl3_reached,
  COUNT(*) FILTER (WHERE previous_value::integer < 4 AND new_value = '4') AS tl4_reached
FROM user_histories
WHERE created_at >= :start_date
  AND created_at <= :end_date
  AND (action = 2 OR action = 15)
GROUP BY date(created_at)
ORDER BY date(created_at)

Explicação da Consulta SQL

A consulta funciona selecionando registros da tabela user_histories, que registra alterações nos estados do usuário, incluindo seus níveis de confiança.

Aqui está uma análise da consulta:

  • Parâmetros de Data: 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.
  • date(created_at) AS date: Esta parte da instrução SELECT extrai a data do timestamp created_at e a rotula como ‘date’ para cada registro. Isso será usado para agrupar os resultados por dia.
  • COUNT(*) FILTER (WHERE previous_value::integer < X AND new_value = 'Y') AS tlY_reached: Esta seção conta o número de usuários que progrediram para um nível de confiança específico (Y) a partir de um nível de confiança inferior (inferior a X). A cláusula FILTER é usada para incluir apenas as linhas que correspondem à condição especificada. A conversão ::integer é usada para converter previous_value em um inteiro para comparação. Existem quatro dessas colunas de contagem, uma para cada nível de confiança de 1 a 4.
  • FROM user_histories: Isso especifica a tabela da qual recuperar os dados.
  • WHERE created_at >= :start_date AND created_at <= :end_date: Isso filtra os registros para incluir apenas aqueles dentro do intervalo de datas especificado.
  • AND (action = 2 OR action = 15): Isso filtra os registros para incluir apenas aqueles em que a ação indica uma mudança no nível de confiança. As ações específicas que correspondem às mudanças de nível de confiança são representadas pelos números 2 e 15.
  • GROUP BY date(created_at): Isso agrupa os resultados por data, para que você obtenha uma contagem de alterações de nível de confiança para cada dia dentro do intervalo especificado.
  • ORDER BY date(created_at): Isso ordena os resultados cronologicamente por data.

Exemplo de Resultados

date tl1_reached tl2_reached tl3_reached tl4_reached
2024-01-01 1 3 1 0
2024-01-02 5 1 0 1
2024-01-03 8 2 3 0
… … … … …
4 curtidas