Relatório do Painel - Proporção de Sinalização de Usuário

Dados e relatórios
, ,
1

Este é um relatório de painel em SQL para a Proporção de Sinalização de Usuários.

Este relatório de painel gera uma lista de usuários ordenada pela proporção de respostas da equipe às suas sinalizações (concordadas e discordadas). O relatório inclui informações sobre a atividade de sinalização de cada usuário, focando especificamente no número de sinalizações que foram concordadas, discordadas ou ignoradas por moderadores, bem como uma pontuação calculada que representa o desempenho de sinalização do usuário.

O relatório é útil para administradores identificarem usuários que sinalizam conteúdo inadequado com precisão, avaliarem o envolvimento do usuário na moderação da comunidade, fornecerem feedback sobre práticas de sinalização e avaliarem o desempenho de sinalização do usuário para potenciais funções de moderador.

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

SELECT
  u.id AS user_id,
  u.username,
  CASE
    WHEN u.silenced_till IS NOT NULL THEN 't'
    ELSE 'f'
  END AS silenced,
  SUM(CASE WHEN rs.status = 2 THEN 1 ELSE 0 END)::numeric AS disagreed_flags,
  SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric AS agreed_flags,
  SUM(CASE WHEN rs.status = 3 THEN 1 ELSE 0 END)::numeric AS ignored_flags,
  (
    CASE
      WHEN SUM(CASE WHEN rs.status = 2 THEN 1 ELSE 0 END)::numeric = 0 THEN
        SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric * SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric
      ELSE
        ROUND(
          (1 - (SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric / SUM(CASE WHEN rs.status = 2 THEN 1 ELSE 0 END))) *
          (SUM(CASE WHEN rs.status = 2 THEN 1 ELSE 0 END) - SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric)
        )
    END
  ) AS score
FROM
  users AS u
  INNER JOIN reviewable_scores AS rs ON rs.user_id = u.id
WHERE
  u.id > 0
  AND rs.created_at >= :start_date
  AND rs.created_at <= :end_date
GROUP BY
  u.id,
  u.username,
  u.uploaded_avatar_id,
  u.silenced_till
ORDER BY
  score DESC
LIMIT 100

Explicação da Consulta SQL

Aqui está uma análise da consulta:

  • Parâmetros: A consulta aceita dois parâmetros, :start_date e :end_date, que permitem ao usuário especificar o intervalo de datas para o relatório. Ambos os parâmetros de data aceitam o formato de data AAAA-MM-DD.
  • Cláusula SELECT: A consulta seleciona as seguintes colunas:
    • user_id: O identificador exclusivo do usuário.
    • username: O nome de usuário do usuário.
    • silenced: Um valor booleano indicando se o usuário está atualmente silenciado.
    • disagreed_flags: O número total de sinalizações de usuários com as quais os moderadores discordaram.
    • agreed_flags: O número total de sinalizações de usuários com as quais os moderadores concordaram.
    • ignored_flags: O número total de sinalizações de usuários que foram ignoradas pelos moderadores.
    • score: Uma pontuação calculada representando o desempenho de sinalização do usuário.
  • Cláusula FROM: A consulta une a tabela users com a tabela reviewable_scores no user_id para obter informações de sinalização relacionadas a cada usuário.
  • Cláusula WHERE: A consulta filtra os dados para incluir apenas os registros de usuários com um id maior que 0 e onde a data created_at das pontuações revisáveis esteja dentro do intervalo de datas especificado.
  • Cláusula GROUP BY: A consulta agrupa os resultados por user_id, username, uploaded_avatar_id e silenced_till para agregar os dados de sinalização de cada usuário.
  • Cláusula ORDER BY: A consulta ordena os resultados pela score calculada em ordem decrescente para mostrar primeiro os usuários com o melhor desempenho de sinalização.
  • Cláusula LIMIT: A consulta limita os resultados aos 100 principais usuários com base na pontuação calculada.

Exemplo de Resultados

user username silenced disagreed_flags agreed_flags ignored_flags score
user_1_id user_1 f 0.0 10.0 3.0 100.0
user_2_id user_2 f 0.0 6.0 3.0 36.0
user_3_id user_3 f 0.0 4.0 0.0 16.0
… … … … … … …
1 curtida