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

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?

[image] Jumanji: Can this be tied to a specific PM, like the welcome PM? 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.

Dashboard Report - System

Community Building Data & reporting

Jumanji March 20, 2024, 12:56am 4

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

Topic		Replies	Views
Dashboard Report - Notify User Data & reporting sql-query , dashboard-reports , dashboard-sql	0	357	January 17, 2024
Dashboard Report - User-to-User Data & reporting sql-query , dashboard-reports , dashboard-sql	0	590	December 13, 2023
Dashboard Report - Notify Moderators Data & reporting sql-query , dashboard-reports , dashboard-sql	0	363	January 17, 2024
SQL query to get the queue of all the PMs in the system Product Support	2	2108	January 27, 2016
Identify private message interactions between users Data & reporting sql-query	1	924	October 13, 2020

Dashboard Report - System

Related topics