Dashboard Report - System

Data & reporting
, ,
1

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

Can this be tied to a specific PM, like the welcome PM?

I’d like to know how many of those go out daily.

Thoughts?

1 Like
3

Yes, the best way to add this in would be to add a section to the WHERE statement in the query, filtering by topic title.

For example:

WHERE archetype = 'private_message'
  AND subtype = 'system_message'
  AND created_at BETWEEN :start_date AND :end_date
  AND title = 'Greetings!'

Would find all of the Greetings! messages.

Note that some of the system messages do not include a real user, so removing the line AND user_id > 0 may be necessary with this type of query.

You may also want to remove AND deleted_at IS NULL to still count welcome messages that users might delete.

You could use regex to match topics with a similar title.

To match a topic by title using a regular expression (regex) in PostgreSQL, you can use the ~ operator, which matches a regular expression against a string. The query structure would look like this:

SELECT *
FROM topics
WHERE title ~ 'YourRegexPatternHere'

Replace 'YourRegexPatternHere' with the actual regex pattern you want to match against the title field.

For example, if you’re looking for topics with titles that contain the word “Welcome” (case-insensitive), you could use:

SELECT *
FROM topics
WHERE title ~* 'Welcome'

The ~* operator is used for case-insensitive matching.

3 Likes
4

Oh this is awesome. Thanks so much! I’ll give this a try!

1 Like