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.
-- [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 ofYYYY-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 theviewed_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 |