Relatório do Painel - Favoritos

Este é um relatório de painel em SQL para Favoritos.

Este relatório de painel fornece uma contagem diária de favoritos criados dentro de um intervalo de datas especificado.

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

WITH bookmark_counts AS (
  SELECT
    DATE(bookmarks.created_at) AS bookmark_date,
    COUNT(DISTINCT CASE WHEN bookmarkable_type = 'Topic' THEN bookmarks.id END) AS topic_bookmarks,
    COUNT(DISTINCT CASE WHEN bookmarkable_type = 'Post' THEN bookmarks.id END) AS post_bookmarks
  FROM bookmarks
  WHERE bookmarks.created_at::date BETWEEN :start_date AND :end_date
  GROUP BY bookmark_date
)
SELECT
  bookmark_date,
  topic_bookmarks + post_bookmarks AS total_bookmarks,
  topic_bookmarks,
  post_bookmarks
FROM bookmark_counts
ORDER BY bookmark_date

Explicação da Consulta SQL

Parâmetros

Para gerar o relatório, você precisa fornecer dois parâmetros: :start_date e :end_date. Esses parâmetros definem o intervalo de datas para o qual você deseja analisar a atividade de favoritos. Ambos os parâmetros de data aceitam o formato de data AAAA-MM-DD.

Bookmark_Counts (CTE)

Uma Expressão Comum de Tabela (CTE) chamada bookmark_counts é usada para organizar os dados antes da seleção final. A CTE executa as seguintes ações:

• Filtragem de Data: Filtra os favoritos por sua data de criação para incluir apenas aqueles criados dentro das datas de início e fim fornecidas (:start_date e :end_date).
• Agrupamento por Data: Agrupa os resultados pela data de criação dos favoritos, garantindo que as contagens sejam agregadas diariamente.
• Contagem de Favoritos: Conta o número de favoritos para tópicos e posts separadamente, usando DISTINCT para garantir que cada favorito seja contado apenas uma vez. Isso é feito usando agregação condicional com instruções CASE:
  • topic_bookmarks conta os favoritos onde bookmarkable_type é ‘Topic’.
  • post_bookmarks conta os favoritos onde bookmarkable_type é ‘Post’.

Consulta Principal

Após a CTE ter organizado os dados, a instrução SELECT final recupera as seguintes colunas:

1. bookmark_date: A data para a qual as contagens são agregadas.
2. total_bookmarks: A soma dos favoritos de tópicos e posts para cada dia.
3. topic_bookmarks: A contagem de favoritos para tópicos para cada dia. Ex:
   
   

   image1249×198 17.3 KB
4. post_bookmarks: A contagem de favoritos para posts para cada dia. Ex:
   
   

   image1121×125 5.07 KB

Os resultados são então ordenados por bookmark_date para apresentar os dados em ordem cronológica.

Resultados de Exemplo