Отчет панели управления - Закладки

[SaraDev (Sara Devlaeminck)]

Это SQL-версия отчета панели управления для закладок.

Этот отчет панели управления предоставляет ежедневное количество созданных закладок в указанном диапазоне дат.

-- [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

Пояснение к SQL-запросу

Параметры

Для формирования отчета необходимо указать два параметра: :start_date и :end_date. Эти параметры определяют диапазон дат, для которого вы хотите проанализировать активность закладок. Оба параметра даты принимают формат YYYY-MM-DD.

Bookmark_Counts (CTE)

Общее табличное выражение (CTE) с именем bookmark_counts используется для организации данных перед финальным выбором. CTE выполняет следующие действия:

• Фильтрация по дате: Она фильтрует закладки по дате их создания, включая только те, которые были созданы в пределах указанных начальной и конечной дат (:start_date и :end_date).
• Группировка по дате: Она группирует результаты по дате создания закладок, обеспечивая агрегацию подсчетов на ежедневной основе.
• Подсчет закладок: Она подсчитывает количество закладок для тем и постов отдельно, используя DISTINCT, чтобы гарантировать, что каждая закладка будет учтена только один раз. Это делается с помощью условной агрегации с использованием операторов CASE:
  • topic_bookmarks подсчитывает закладки, где bookmarkable_type равен ‘Topic’.
  • post_bookmarks подсчитывает закладки, где bookmarkable_type равен ‘Post’.

Основной запрос

После того как CTE организовала данные, финальный оператор SELECT извлекает следующие столбцы:

1. bookmark_date: Дата, для которой агрегируются подсчеты.
2. total_bookmarks: Сумма закладок для тем и постов за каждый день.
3. topic_bookmarks: Количество закладок для тем за каждый день. Пример:
   
   
4. post_bookmarks: Количество закладок для постов за каждый день. Пример:
   
   

Результаты затем сортируются по bookmark_date, чтобы представить данные в хронологическом порядке.

Пример результатов

bookmark_date	total_bookmarks	topic_bookmarks	post_bookmarks
2023-12-01	16	1	15
2023-12-02	4	1	3
2023-12-03	8	0	8
2023-12-04	19	9	10
2023-12-05	18	3	15
…	…	…	…