Dashboard Report - System

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

This dashboard report provides a daily count of the number of personal messages sent automatically by the system.

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01

SELECT
  DATE(created_at) AS day,
  COUNT(*) AS notifications_count
FROM topics
WHERE archetype = 'private_message'
  AND subtype = 'system_message'
  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 system_message 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.
  • SELECT: The query selects two fields:
    • DATE(created_at) AS day: This extracts the date part of the created_at timestamp, effectively grouping records by the day they were created.
    • COUNT(*) AS notifications_count: This counts the total number of system-generated PMs for each day.
  • FROM: Specifies the topics table as the data source, which contains records of all topics, including private messages.
  • WHERE: Contains multiple filters to narrow down the dataset:
    • archetype = 'private_message': Only includes entries that are private messages.
    • subtype = 'system_message': Further narrows down the selection to only system-generated messages.
    • created_at BETWEEN :start_date AND :end_date: Filters the PMs to those created within the range specified by the parameters.
    • deleted_at IS NULL: Excludes messages that have been deleted.
    • user_id > 0: Ensures that messages are associated with real user accounts rather than the system or anonymous accounts.
  • GROUP BY: Groups results based on the day they were created.
  • ORDER BY: Orders the final result set by the day in ascending order, ensuring a chronological sequence of daily counts.

Example Results

day notifications_count
2024-01-01 5
2024-01-02 7
2024-01-03 11
2024-01-04 14
2024-01-05 8
1 Like