Dashboard Report - Daily Engaged Users

This is an SQL version of the Dashboard Report for Daily Engaged Users.

The SQL report will count the number of unique users that have liked or posted on a site each day.

-- date :start_date
-- date :end_date

  ua.created_at::date AS day,
  COUNT(DISTINCT ua.user_id) AS engaged_users
FROM user_actions ua
INNER JOIN topics t on t.id = ua.target_topic_id
WHERE ua.created_at::date BETWEEN :start_date AND :end_date
    AND ua.action_type IN (1, 4, 5, 12)
GROUP BY ua.created_at::date
ORDER BY ua.created_at::date ASC 

Breakdown of the Query:

  • Date Parameters: The query accepts two date parameters, :start_date and :end_date, which allow you to specify the range for the report.
  • Selection: It selects the date of the action (ua.created_at::date) and counts the distinct user IDs (COUNT(DISTINCT ua.user_id)) who performed the actions, aliasing this count as engaged_users.
  • Data Source: The query pulls from the user_actions table (ua) and joins it with the topics table (t) to focus on actions related to topics.
  • Action Types: It filters the actions to include only those with types 1, 4, 5, and 12. These action types correspond to the following specific activities:
    • 1: Like
    • 4: New Topic
    • 15: Reply
    • 12: New Private Message
  • Grouping and Ordering: The results are grouped by the date of the action and ordered in ascending order, giving you a chronological view of the activity.

Example Results

day engaged_users
2023-01-01 136
2023-01-02 124
2023-01-03 187
2023-01-04 287
2023-01-05 110
2023-01-06 95
2023-01-07 73

This one is quite interesting when you see it laid out. It would be good to maybe have a version that included Reacting and Voting (polls, topic votes, post votes - maybe even post voting comments). :thinking: