Relatório do Painel - Bandeiras

Dados e relatórios
, ,
1

Este é um relatório de painel em versão SQL para Sinalizações

Este relatório fornece uma contagem de postagens sinalizadas dentro de uma categoria específica opcional (e opcionalmente suas subcategorias) em um determinado intervalo de datas. Essas informações podem ser úteis para gerentes de comunidade e moderadores entenderem o volume de sinalizações levantadas em certas partes do fórum, o que pode ajudar a identificar tendências e áreas que podem exigir atenção ou moderação adicionais.

-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2024-01-01
-- null int :category_id = 0
-- boolean :include_subcategories = false

WITH RECURSIVE subcategories AS (
    SELECT id FROM categories WHERE id = :category_id
    UNION ALL
    SELECT c.id FROM categories c
    INNER JOIN subcategories sc ON c.parent_category_id = sc.id
)

SELECT
    DATE(r.created_at) AS flag_date,
    COUNT(r.id) AS flags_count
FROM reviewables r
LEFT JOIN categories c ON r.category_id = c.id
WHERE r.type = 'ReviewableFlaggedPost'
    AND r.created_at::DATE BETWEEN :start_date AND :end_date
    AND (:category_id = 0 OR (
        :include_subcategories
        AND r.category_id IN (SELECT id FROM subcategories)
        OR NOT :include_subcategories
        AND r.category_id = :category_id
    ))
GROUP BY flag_date
ORDER BY flag_date

Explicação da Consulta SQL

A consulta SQL foi projetada para contar o número de sinalizações (que são representadas por registros ReviewableFlaggedPost na tabela reviewables) criadas em cada data dentro do intervalo de datas e categoria especificados. Ela usa uma Expressão de Tabela Comum (CTE) recursiva para incluir opcionalmente subcategorias da categoria escolhida.

Parâmetros

  • :start_date: O início do intervalo de datas para o qual contar as sinalizações.
  • :end_date: O fim do intervalo de datas para o qual contar as sinalizações.
  • :category_id: O ID da categoria para a qual contar as sinalizações. Se definido como 0, o filtro por categoria não é aplicado.
  • :include_subcategories: Um valor booleano que determina se as subcategorias da categoria especificada devem ser incluídas na contagem.

Detalhamento da Consulta

  1. CTE Recursiva (subcategories):
    • Esta CTE começa selecionando a categoria com o ID fornecido pelo parâmetro :category_id.
    • Em seguida, seleciona recursivamente todas as subcategorias dessa categoria, unindo a tabela categories com a CTE subcategories no campo parent_category_id.
    • O resultado é uma lista de todas as subcategorias (incluindo a categoria inicial) que serão usadas para filtragem.
  2. Consulta Principal:
    • A consulta seleciona duas colunas: a data de criação da sinalização (flag_date) e a contagem de sinalizações (flags_count) para essa data.
    • Ela une a tabela reviewables com a tabela categories para associar cada sinalização à sua categoria.
  3. Filtros:
    • A consulta filtra os registros de reviewables para incluir apenas aqueles do tipo ReviewableFlaggedPost, que representa postagens sinalizadas.
    • Ela garante que a data created_at das sinalizações esteja dentro do intervalo de datas especificado (:start_date a :end_date).
    • Ela aplica um filtro condicional com base nos parâmetros :category_id e :include_subcategories:
      • Se :category_id for 0, nenhum filtro de categoria é aplicado.
      • Se :include_subcategories for true, a consulta inclui sinalizações da categoria inicial e de todas as suas subcategorias.
      • Se :include_subcategories for false, a consulta inclui sinalizações apenas da categoria inicial.
  4. Agrupamento e Ordenação:
    • Os resultados são agrupados pela data de criação da sinalização para fornecer uma contagem por dia.
    • Os resultados são ordenados por data para apresentar os dados em uma sequência cronológica.

Exemplo de Resultados

flag_date flags_count
2023-11-19 2
2023-11-22 1
2023-11-23 1
2023-11-24 2
2023-11-26 1
… …
1 curtida