Dashboard Report - User-to-User

data & reporting
, ,
1

This is an SQL version of the Dashboard Report for User-to-User (with replies) and User-to-User (excluding replies).

This report counts the number of personal messages sent by users each day, within a specified date range, providing insight into the activity of user engagement in private communication.

--[params]
-- date :start_date = 2024-01-06
-- date :end_date = 2024-02-07
-- boolean :include_replies = false

SELECT 
    p.created_at::date as day,
    COUNT(p.user_id) as personal_messages
FROM posts p
INNER JOIN topics t ON (p.topic_id = t.id)
WHERE archetype = 'private_message'
    AND p.user_id > 0
    AND t.subtype = 'user_to_user'
    AND p.deleted_at ISNULL
    AND t.deleted_at ISNULL
    AND p.created_at::date BETWEEN :start_date AND :end_date
    AND (:include_replies = true OR p.post_number = 1)
GROUP BY p.created_at::date
ORDER BY p.created_at::date

SQL Query Explanation

This SQL query performs the following actions:

  • Parameters:
    • :start_date and :end_date, which are used to filter the data within the specified date range. Both date parameters accept the format of YYYY-MM-DD.
    • :include_replies to determine whether replies to initial messages should be included in the count. Set to false by default to mirror the behavior of the User-to-User (excluding replies) report. Setting this parameter to true will mirror the behavior of the User-to-User (with replies) report.
  • Data Selection:
    • The query selects the created_at date from the posts table, which represents the date when each message was created.
  • Join Operation:
    • The query performs an INNER JOIN between the posts and topics tables on their id fields. This join is crucial as it links each post to its corresponding topic, allowing us to filter out only those posts that are part of a personal message conversation.
  • Filtering Criteria: The query applies several filters:
    • It only considers posts that are part of a topic with an archetype of ‘private_message’, ensuring that only personal messages are counted.
    • It filters out any posts that are not associated with a user by ensuring user_id is greater than 0.
    • It further narrows down the results to topics with a subtype of ‘user_to_user’, which indicates a personal message between individual users.
    • It excludes any posts or topics that have been deleted by checking that deleted_at is NULL for both tables.
    • It applies the date range filter to include only posts created between the :start_date and :end_date.
    • If :include_replies is false, only the first post in each topic is counted (p.post_number = 1), excluding replies.
  • Aggregation: The query groups the results by the date on which the posts were created, which allows for counting the number of messages sent on each date.
  • Sorting: Finally, the query orders the results by the date in ascending order, providing a chronological sequence of user-to-user personal message activity.

Example Results

day personal_messages
2023-11-11 92
2023-11-12 57
2023-11-13 345
2023-11-14 124
2023-11-15 56
4 Likes