Dashboard Report - Notify User

Data & reporting
, ,
1

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

This dashboard report provides a daily count of the number of times users have been privately notified by a flag on one of their posts.

-- [params]
-- date :start_date = 2023-12-15
-- date :end_date = 2024-01-16

SELECT
  DATE(created_at) AS day,
  COUNT(1) AS notifications_count
FROM topics
WHERE archetype = 'private_message'
  AND subtype = 'notify_user'
  AND created_at BETWEEN :start_date AND :end_date
  AND deleted_at IS NULL
  AND user_id > 0
GROUP BY DATE(created_at)
ORDER BY day

SQL Query Explanation

The query works by extracting data from the topics table – specifically, those that qualify as private messages to users with a subtype of notify_user within a given time frame. Let’s break it down:

  • Date Parameters:
    • The query accepts two parameters, :start_date and :end_date, which define the date range for the report. Both date parameters accept the date format of YYYY-MM-DD.
  • Selection and Count:
    • DATE(created_at) converts the created_at timestamp into a date format and labels it as day. This allows grouping and counting messages by the day they were created.
    • COUNT(1) is used to count the number of entries for each day, labeling this count as notifications_count.
  • Filtering Criteria:
    • The query filters only those entries where the archetype is 'private_message', ensuring we’re looking solely at messages and not at other types of topics.
    • The subtype is restricted to 'notify_user', isolating cases where users have been notified about flagged posts.
    • It checks that the created_at timestamp of the message is between the specified :start_date and :end_date.
    • Messages that have been deleted (deleted_at IS NULL) are excluded from the count to ensure the report reflects only active communications.
    • Finally, it ensures that the messages are associated with a valid user by confirming user_id > 0.
  • Grouping and Ordering:
    • The query uses GROUP BY DATE(created_at) to aggregate the results by each day within the specified date range.
    • Then it orders the results chronologically with ORDER BY day, making the report easy to read and interpret.

Example Results

day notifications_count
2023-12-17 1
2023-12-18 1
2023-12-21 1
2023-12-23 1
2023-12-26 1
1 Like