Dashboard Report - User Profile Views

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