Dashboard Report - User Profile Views

[SaraDev (Sara Devlaeminck)]

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