Relatório do Painel - Principais Usuários por Curtidas Recebidas

Dados e relatórios
, ,
1

Este é um relatório de painel em SQL para os principais usuários por curtidas recebidas.

Este relatório de painel mostra os principais usuários em um site pelo número de curtidas recebidas em todas as postagens. O relatório ajuda os administradores a identificar usuários altamente engajados contando as curtidas recebidas em um determinado período, auxiliando no reconhecimento de membros ativos, na avaliação da qualidade do conteúdo, no monitoramento de tendências de engajamento do usuário e na tomada de decisões informadas de gerenciamento da comunidade, como a seleção de moderadores.

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

SELECT
ua.user_id AS user_id,
u.username as username,
COUNT(*) likes_received
FROM user_actions ua
INNER JOIN users u on ua.user_id = u.id
WHERE ua.created_at::date BETWEEN :start_date AND :end_date
AND ua.action_type = 2
GROUP BY ua.user_id, u.username, u.uploaded_avatar_id
ORDER BY likes_received DESC

Explicação da Consulta SQL

A consulta funciona da seguinte forma:

  • 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.
  • Seleção de Dados: A consulta seleciona IDs de usuário e nomes de usuário da tabela user_actions (ua) e da tabela users (u), que são unidas usando o comando INNER JOIN do SQL na condição de que ua.user_id corresponda a u.id.
  • Filtragem de Dados: A cláusula WHERE é usada para filtrar ações que se enquadram no intervalo de datas especificado e considera apenas ações do tipo was_liked, que é representado por action_type = 2.
  • Agregação: A função COUNT(*) em combinação com a cláusula GROUP BY agrupa os resultados por user_id, username e uploaded_avatar_id, e conta o número de curtidas que cada usuário recebeu.
  • Ordenação de Resultados: A cláusula ORDER BY então ordena os dados agregados de forma decrescente com base em likes_received, para que o usuário com o maior número de curtidas recebidas esteja no topo do relatório.

A consulta produz uma lista de usuários juntamente com a contagem de curtidas que eles receberam no intervalo de datas fornecido, ordenados do maior para o menor.

Exemplo de Resultados

user username likes_received
user1 user1 748
user2 user2 324
user3 user3 308
… … …
2 curtidas