Relatório do Painel - Principais Usuários Ignorados / Silenciados

[SaraDev (Sara Devlaeminck)]

Este é um relatório de painel em versão SQL para usuários mais ignorados/silenciados.

Este relatório de painel fornece aos administradores informações sobre quais usuários na plataforma foram ignorados ou silenciados por outros membros entre datas de início e fim especificadas. Um usuário ignorado é aquele que foi seletivamente filtrado por um membro para que suas postagens não sejam visíveis para ele, enquanto um usuário silenciado é aquele de quem um membro optou por não receber notificações.

Este relatório pode identificar usuários que estão causando atrito dentro de uma comunidade, ou cujo comportamento está levando outros a não quererem interagir com eles. O relatório pode ajudar na tomada de decisões de moderação, como entrar em contato com usuários frequentemente ignorados ou silenciados para abordar seu comportamento, ou entender de forma mais ampla se há problemas na comunidade que exigem atenção.

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

WITH ignored_users AS (
    SELECT
        ignored_user_id AS user_id,
        COUNT(*) AS ignores_count
    FROM ignored_users
    WHERE created_at >= :start_date
      AND created_at <= :end_date
    GROUP BY ignored_user_id
    ORDER BY COUNT(*) DESC
),
muted_users AS (
    SELECT
        muted_user_id AS user_id,
        COUNT(*) AS mutes_count
    FROM muted_users
    WHERE created_at >= :start_date
      AND created_at <= :end_date
    GROUP BY muted_user_id
    ORDER BY COUNT(*) DESC
)

SELECT
    u.id AS user_id,
    u.username AS username,
    ig.ignores_count AS ignores_count,
    COALESCE(mu.mutes_count, 0) AS mutes_count,
    ig.ignores_count + COALESCE(mu.mutes_count, 0) AS total
FROM users AS u
JOIN ignored_users AS ig ON ig.user_id = u.id
LEFT OUTER JOIN muted_users AS mu ON mu.user_id = u.id
ORDER BY total DESC

Explicação da Consulta SQL

Esta consulta SQL funciona em várias etapas:

• 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ões de Tabela Comuns (CTEs):
  • A CTE ignored_users seleciona o ignored_user_id e conta quantas vezes esse ID aparece na tabela ignored_users (indicando quantas vezes o usuário foi ignorado), filtrando os resultados entre :start_date e :end_date.
  • A CTE muted_users é semelhante, mas seleciona o muted_user_id da tabela muted_users e conta as instâncias dentro dos filtros de data.
• Instrução SELECT Principal: Seleciona os detalhes do usuário da tabela users e as contagens das CTEs ignored_users e muted_users.
  • ignores_count é retirado diretamente da CTE ignored_users.
  • mutes_count é retirado da CTE muted_users, mas se for NULL (significando que o usuário não foi silenciado), ele é substituído por 0 usando a função COALESCE.
  • Um total é calculado somando os ignorados e os silenciados.
• JOINS: A consulta principal utiliza um JOIN com ignored_users para incluir todos os usuários que foram ignorados pelo menos uma vez, e um LEFT OUTER JOIN com muted_users para incluir também usuários que podem não ter sido silenciados.
• ORDER BY: O relatório é ordenado pelo total de ignorados e silenciados em ordem decrescente, mostrando os usuários mais ‘problemáticos’ no topo do relatório.

Exemplo de Resultados

user	username	ignores_count	mutes_count	total
user1	user1	4	1	5
user2	user2	3	0	3
user3	user3	1	2	3