# Dashboard Report - Bookmarks

**URL:** https://meta.discourse.org/t/dashboard-report-bookmarks/289129
**Category:** Data & reporting
**Tags:** sql-query, dashboard-reports, dashboard-sql
**Created:** [December 19, 2023, 1:06am UTC](https://meta.discourse.org/t/dashboard-report-bookmarks/289129 "2023-12-19T01:06:01Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![SaraDev](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/saradev/32/335139_2.png) [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [December 19, 2023, 1:06am UTC](https://meta.discourse.org/t/dashboard-report-bookmarks/289129/1 "2023-12-19T01:06:01Z")

</div>

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.

```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 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:  
 ![image](https://global.discourse-cdn.com/meta/original/4X/7/c/d/7cd564506d4a8131353dc04cd7c4bb3f6a19e6bd.png)
4. `post_bookmarks`: The count of bookmarks for posts for each day. Ex:  
 ![image](https://global.discourse-cdn.com/meta/original/4X/7/8/7/787b574dcdce4c44ce49b081c29d3b0af3035b20.png)

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