Relatório do Painel - Principais Usuários por Curtidas Recebidas de Várias Pessoas

Esta é uma versão SQL do Relatório de Painel para Principais Usuários por Curtidas Recebidas de uma Variedade de Pessoas.

Este relatório de painel mostra os principais usuários que receberam curtidas de uma ampla gama de usuários de um site, em um intervalo de datas especificado. A consulta foi projetada para descobrir quais usuários receberam o maior número de curtidas em suas postagens, com cada contagem de curtidas representando o número de usuários exclusivos que curtiram suas postagens.

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

SELECT
    p.user_id,
    u.username,
    COUNT(DISTINCT ua.user_id) AS likes
FROM
    user_actions ua
    INNER JOIN posts p ON p.id = ua.target_post_id
    INNER JOIN users u ON p.user_id = u.id
WHERE
    ua.created_at::date BETWEEN :start_date AND :end_date
    AND ua.action_type = 1
    AND p.user_id > 0
GROUP BY
    p.user_id,
    u.username
ORDER BY
    likes DESC

Explicação da Consulta SQL

• 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: As principais colunas selecionadas para a saída são:
  • p.user_id: O ID do usuário do autor da postagem.
  • u.username: O nome do usuário correspondente.
  • likes: Uma contagem dos IDs de usuário distintos (ua.user_id) que ‘curtiram’ as postagens, garantindo que as curtidas exclusivas por usuário sejam calculadas.
• Junções: Existem duas junções internas:
  • user_actions ua com posts p: Corresponde às ações às suas postagens correspondentes.
  • posts p com users u: Conecta as postagens aos seus autores, facilitando a recuperação de informações do usuário.
• Filtragem:
  • A cláusula WHERE aplica vários filtros:
    • ua.created_at::date BETWEEN :start_date AND :end_date limita as ações consideradas àquelas dentro dos parâmetros.
    • ua.action_type = 1 isola as ações de ‘curtir’ de outros tipos de ações do usuário.
    • p.user_id > 0 exclui quaisquer curtidas associadas a usuários não registrados (como usuários do sistema ou anônimos).
• Agrupamento:
  • O conjunto de resultados é agrupado por p.user_id e u.username. Isso garante que as curtidas sejam agregadas por usuário e nome de usuário correspondente.
• Ordenação:
  • Os resultados são ordenados pela contagem de curtidas (likes DESC), apresentando primeiro os usuários mais apreciados.

Exemplo de Resultados