Dashboard Report - Bookmarks

[SaraDev (Sara Devlaeminck)]

This is an SQL version of the Dashboard Report for Bookmarks.

This dashboard report provides a daily count of bookmarks created within a specified date range.

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

Parameters

To generate the report, you need to provide two parameters: :start_date and :end_date . These parameters define the date range for which you want to analyze the bookmark activity. Both date parameters accept the date format of YYYY-MM-DD .

Bookmark_Counts (CTE)

A Common Table Expression (CTE) named bookmark_counts is used to organize the data before the final selection. The CTE performs the following actions:

• Date Filtering: It filters the bookmarks by their creation date to include only those created within the provided start and end dates (:start_date and :end_date).
• Date Grouping: It groups the results by the creation date of the bookmarks, ensuring that the counts are aggregated on a daily basis.
• Counting Bookmarks: It counts the number of bookmarks for topics and posts separately, using DISTINCT to ensure that each bookmark is counted only once. This is done using conditional aggregation with CASE statements:
  • topic_bookmarks counts the bookmarks where bookmarkable_type is ‘Topic’.
  • post_bookmarks counts the bookmarks where bookmarkable_type is ‘Post’.

Main Query

After the CTE has organized the data, the final SELECT statement retrieves the following columns:

1. bookmark_date: The date for which the counts are aggregated.
2. total_bookmarks: The sum of topic and post bookmarks for each day.
3. topic_bookmarks: The count of bookmarks for topics for each day. Ex:
   
   
4. post_bookmarks: The count of bookmarks for posts for each day. Ex:
   
   

The results are then ordered by bookmark_date to present the data in chronological order.

Example Results

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