Dashboard Report - Likes

Data & reporting
, ,
1

This is an SQL version of the Dashboard Report for Likes.

This query provides a report of the total number of likes given to all posts on a site, on a day-to-day basis, within a specified date range.

-- [params]
-- date :start_date = 2023-12-08
-- date :end_date = 2024-01-10

WITH date_range AS (
  SELECT date_trunc('day', series) AS date
  FROM generate_series(
    :start_date::timestamp,
    :end_date::timestamp,
    '1 day'::interval
  ) series
)

SELECT
  dr.date::date,
  COALESCE(pa.likes_count, 0) AS likes_count
FROM
  date_range dr
LEFT JOIN (
  SELECT
    date_trunc('day', pa.created_at) AS action_date,
    COUNT(*) AS likes_count
  FROM post_actions pa
  WHERE pa.post_action_type_id = 2 
    AND pa.created_at >= :start_date
    AND pa.created_at <= (:end_date::date + 1)
  GROUP BY action_date
) pa ON dr.date = pa.action_date
ORDER BY dr.date

SQL Query Explanation

The main structure of the query builds upon a CTE (Common Table Expression) named date_range, which is used to generate a series of timestamps, each representing a different day within the user-defined period.

Parameters

The query accepts two parameters:

  • :start_date: The beginning of the period for which to generate the report.
  • :end_date: The end of the period for which to generate the report.

Common Table Expression: date_range

  • generate_series is a function that creates a set of timestamps from :start_date to :end_date, incremented by an interval of ‘1 day’.
  • date_trunc('day', series) truncates the timestamp to the start of the day, effectively normalizing all timestamps to 00:00:00 of their respective days.
  • The result is a set of dates, one per row, covering the entire range from :start_date to :end_date.

Subquery: Counting Likes

A subquery is used to calculate the number of likes for each day by counting rows from the post_actions table.

  • This query filters post_actions for entries where the action type signifies a like (where post_action_type_id = 2 means ‘like’).
  • It filters the actions to the date range, adding one day to the end date to include likes given on the last day.
  • It groups the results by day and counts the likes for each day.

Main Query: Merging Results

The final section of the query merges the set of all dates from the date_range CTE with the count of likes from the subquery.

  • A LEFT JOIN ensures that all dates from date_range are included in the result, even if there are no corresponding like actions for a given date (no joins found in the subquery).
  • COALESCE is used to replace NULL counts (from days without likes) with zero, ensuring the report accurately reflects days without any like activity.
  • The final result set is ordered by date to provide a chronological view of the likes given over the specified period.

Example Results

date likes_count
2023-12-08 123
2023-12-09 156
2023-12-10 278
2023-12-11 134
2023-12-12 89
2 Likes
2

Does this one need a AND pa.deleted_at IS NULL to filter out the Likes cast and then removed to marry up, or would that be a possible change to the dashboard query itself?

2 Likes
3

As it is currently, the dashboard report does include deleted likes, so adding AND pa.deleted IS NULL would change how this query is matching up with the dashboard report.

Modifying the underlying dashboard report to not include deleted likes could be a good change to consider making to the report though.

2 Likes