Relatório do Painel - Principais Usuários por Curtidas Recebidas de um Usuário com Nível de Confiança Inferior

Dados e relatórios
, ,
1

Este é um relatório de painel em SQL para os principais usuários por curtidas recebidas de um usuário com um nível de confiança inferior.

Este relatório de painel tem como objetivo identificar quais usuários receberam o maior número de curtidas de membros de níveis de confiança inferiores dentro de um período especificado. O relatório foca na interação entre usuários de diferentes níveis de confiança e destaca contas que se destacam no engajamento da comunidade por serem curtidas por aqueles com permissões ou experiência inferiores no fórum.

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

WITH user_liked_tl_lower AS (
    SELECT
        users.id AS user_id,
        users.username,
        users.trust_level,
        COUNT(*) AS likes,
        rank() OVER (
            PARTITION BY users.trust_level
            ORDER BY COUNT(*) DESC
        ) AS rank
    FROM users
    INNER JOIN posts p ON p.user_id = users.id
    INNER JOIN user_actions ua ON ua.target_post_id = p.id AND ua.action_type = 1
    INNER JOIN users u_liked ON ua.user_id = u_liked.id AND u_liked.trust_level < users.trust_level
    WHERE ua.created_at::date BETWEEN :start_date AND :end_date
    GROUP BY users.id
    ORDER BY trust_level DESC, likes DESC
)

SELECT * FROM user_liked_tl_lower
WHERE rank <= 10

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.
  • Expressão de Tabela Comum (CTE):
    • A CTE user_liked_tl_lower é definida no início para reutilização dentro da consulta principal.
    • Ela calcula o número total de curtidas que cada usuário recebeu em suas postagens de usuários com níveis de confiança inferiores dentro do período de tempo fornecido definido por :start_date e :end_date.
  • Junções Internas (Inner Joins):
    • As junções são realizadas entre a tabela users e as tabelas posts e user_actions para filtrar as ações que são ‘curtidas’ (indicadas por action_type = 1).
    • Uma junção adicional à tabela users (u_liked) é feita para garantir que quem curtiu tenha um nível de confiança inferior ao do autor da postagem.
  • Classificação (Ranking):
    • Os resultados são particionados pelo nível de confiança do usuário e ordenados pela contagem de curtidas em ordem decrescente.
    • Uma classificação é atribuída com base no número de curtidas, com empates recebendo a mesma classificação e a próxima classificação pulando números de acordo (esta é uma classificação padrão, não uma classificação densa).
  • Filtragem de resultados na CTE:
    • Apenas as curtidas que foram dadas dentro do período de tempo especificado são contadas.
  • Seleção Final:
    • A consulta principal seleciona todas as colunas da CTE onde a rank é 10 ou inferior.
    • Isso limita os resultados aos 10 principais usuários em cada categoria de nível de confiança que receberam o maior número de curtidas de usuários de níveis de confiança inferiores.

Exemplo de Resultados

user username trust_level likes rank
user1 user1 4 323 1
user2 user2 4 164 2
user3 user3 4 143 3
… … … …
user11 user11 3 45 1
user12 user12 3 34 2
… … … …
3 curtidas