# Dashboard Report - User Profile Views

**URL:** https://meta.discourse.org/t/dashboard-report-user-profile-views/294477
**Category:** Data & reporting
**Tags:** sql-query, dashboard-reports, dashboard-sql
**Created:** [February 7, 2024, 1:07am UTC](https://meta.discourse.org/t/dashboard-report-user-profile-views/294477 "2024-02-07T01:07:57Z")
**Posts on this page:** 1
**Showing post:** 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: [February 7, 2024, 1:07am UTC](https://meta.discourse.org/t/dashboard-report-user-profile-views/294477/1 "2024-02-07T01:07:57Z")

</div>

This is an SQL version of the Dashboard Report for User Profile Views.

This dashboard report is generates statistics about the total number of user profile views per day within a specified date range.

```sql
-- [params]
-- date :start_date = 2024-01-05
-- date :end_date = 2024-02-06

WITH profile_views_per_day AS (
  SELECT
    date_trunc('day', viewed_at) AS day,
    COUNT(*) AS views
  FROM user_profile_views
  WHERE viewed_at >= :start_date AND viewed_at <= :end_date
  GROUP BY day
)
SELECT
  day::date,
  views
FROM profile_views_per_day
ORDER BY day

```

### SQL Query Explanation

Here’s a breakdown of how the query works:

- **Parameters** : The query accepts two parameters, `:start_date` and `:end_date`, which allow the user to specify the date range for the report. Both date parameters accept the date format of `YYYY-MM-DD` .
- **Common Table Expression (CTE):** The query starts with a CTE named `profile_views_per_day`. This is a temporary result set that we will use in the main query.
- **Date Truncation:** Inside the CTE, the `date_trunc` function is used to truncate the `viewed_at` timestamp to the day. This means that regardless of the time a profile was viewed, it will be counted on the basis of the date alone.
- **Counting Views:** The `COUNT(*)` function counts the number of profile views for each day.
- **Filtering by Date Range:** The `WHERE` clause restricts the count to only include views that occurred between the `:start_date` and `:end_date` parameters, which are set at the top of the query.
- **Grouping Results:** The `GROUP BY` clause groups the results by the truncated day so that the count of views is aggregated per day.
- **Selecting Results:** The main `SELECT` statement retrieves the results from the CTE. It selects the truncated day (cast to a date type) and the number of views for that day.
- **Ordering Results:** Finally, the `ORDER BY` clause ensures that the results are presented in chronological order by day.

### Example Results

| day | views |
| --- | --- |
| 2024-01-05 | 263 |
| 2024-01-06 | 374 |
| 2024-01-07 | 272 |
| 2024-01-08 | 409 |
| 2024-01-09 | 606 |

---

_[View the full topic](https://meta.discourse.org/t/dashboard-report-user-profile-views/294477)._