Relatório do Painel - Posts

Este é um relatório SQL do painel para posts.

Este relatório fornece uma contagem diária de posts criados dentro de um intervalo de datas especificado. Ele foi projetado para rastrear a atividade em tópicos regulares, excluindo posts de mensagens privadas e outros arquétipos especiais.

--[params]
-- date :start_date
-- date :end_date

SELECT 
    p.created_at::date AS "Dia",
    COUNT(p.id) AS "Contagem"
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id AND t.deleted_at ISNULL
WHERE p.created_at::date BETWEEN :start_date AND :end_date
    AND p.deleted_at ISNULL
    AND t.archetype = 'regular'
    AND p.post_type = 1
GROUP BY p.created_at::date
ORDER BY 1  

Explicação da Consulta SQL

• Parâmetros:
  • 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.

A consulta SQL realiza as seguintes operações:

• Seleção de Dados:
  • Seleciona a data (created_at::date) em que cada post foi criado e a converte para o formato de data para ignorar o componente de hora.
  • Também conta o número de posts (COUNT(p.id)) criados em cada data.
• Junções:
  • A consulta une a tabela posts com a tabela topics usando um INNER JOIN. Essa junção garante que apenas posts associados a tópicos existentes sejam considerados.
  • Filtra quaisquer tópicos que foram excluídos (t.deleted_at ISNULL).
• Filtros:
  • Filtra posts para incluir apenas aqueles dentro do intervalo de datas especificado (p.created_at::date BETWEEN :start_date AND :end_date).
  • Exclui posts excluídos (p.deleted_at ISNULL).
  • Restringe os resultados a posts de tópicos regulares (t.archetype = 'regular').
  • Considera apenas posts com p.post_type = 1, excluindo ações de moderador, sussurros e posts de pequenas ações.
• Agrupamento e Ordenação:
  • Os resultados são agrupados pela data de criação do post (GROUP BY p.created_at::date).
  • A saída final é ordenada por data em ordem crescente (ORDER BY 1), onde 1 se refere à primeira coluna na instrução SELECT, que é a data.

Exemplo de Resultados

Isso é ótimo.
Haveria uma maneira de poder definir a categoria/subcategoria em tempo de execução como a data?
E como bônus, podemos listar os resultados por usuário ou definir o usuário também?

O que estou tentando fazer é ver quantos posts são feitos durante (intervalo) em minhas áreas de tickets de suporte pela minha equipe de suporte.

Sim, você pode usar a seguinte consulta para isso:

--[params]
-- date :start_date
-- date :end_date
-- null category_id :category_id 
-- null user_id :user_id
-- boolean :include_subcategories = false

SELECT 
    u.username AS "Usuário",
    p.created_at::date AS "Data",
    COUNT(p.id) AS "Contagem"
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id AND t.deleted_at IS NULL
INNER JOIN users u ON p.user_id = u.id
LEFT JOIN categories c ON t.category_id = c.id
WHERE p.created_at::date BETWEEN :start_date AND :end_date
    AND p.deleted_at IS NULL
    AND t.archetype = 'regular'
    AND p.post_type = 1
    AND (
        :category_id IS NULL 
        OR t.category_id = :category_id
        OR (:include_subcategories AND c.parent_category_id = :category_id)
    )
    AND (:user_id IS NULL OR p.user_id = :user_id)
GROUP BY u.username, p.created_at::date
ORDER BY p.created_at::date ASC, u.username

Parâmetros:

• :start_date & :end_date: Defina o período de tempo do relatório (obrigatório)
• :category_id: Filtro opcional para uma categoria específica
• :user_id: Filtro opcional para um usuário específico
• :include_subcategories: Opção para incluir subcategorias da categoria escolhida

Esta consulta mostra:

• Usuário: Nome de usuário do autor da postagem
• Data: A data de calendário em que as postagens foram criadas
• Contagem: Número de postagens criadas por esse usuário naquela data

Exemplo de Dados:

Obrigado, isso é uma grande ajuda!