Dashboard Report - New Contributors

data & reporting
, ,
1

This is an SQL version of the Dashboard Report for New Contributors.

This SQL dashboard report is designed to give administrators a clear overview of user engagement on their Discourse forum by identifying how many users made their first post on each day within a specified date range.

-- [params]
-- date :start_date = 2023-12-15
-- date :end_date = 2024-01-16

SELECT
  date_trunc('day', p.created_at)::date AS day,
  COUNT(DISTINCT p.user_id) AS new_contributors 
FROM
  posts p
INNER JOIN (
  SELECT
    user_id,
    MIN(created_at) as first_post_date
  FROM
    posts
  WHERE deleted_at IS NULL
  GROUP BY
    user_id
) fp ON p.user_id = fp.user_id
WHERE
  p.created_at = fp.first_post_date
  AND p.created_at BETWEEN :start_date AND (:end_date::date + 1)
GROUP BY
  day
ORDER BY
  day

SQL Query Explanation

The report fetches the count of distinct users who made their first post on each day within the specified date range. It does this by performing the following steps:

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.

Inner Query: Determining the First Post Date for Each User

This report contains a subquery that selects two columns from the posts table: user_id and the earliest created_at timestamp (aliased as first_post_date). The earliest created_at timestamp represents the user’s first post. This subquery includes a WHERE condition that only considers posts that have not been deleted (deleted_at IS NULL). Finally, it groups the results by user_id to ensure we’re only looking at the first post for each user.

Main Query: Counting Users Who Made Their First Post

The main query performs the following operations:

  • JOIN: The main posts table (aliased as p) is joined with the subquery result (aliased as fp) on user_id to match each post with the corresponding user’s first post.
  • Filtering by Date: The WHERE clause includes two conditions - it compares each post’s created_at timestamp with the first_post_date from the subquery to ensure we’re only dealing with first posts, and it checks that the created_at timestamp falls within the specified date range, inclusive of the end_date (+1 day to include the end day fully).
  • Aggregation: The posts are then grouped by the date, truncated to the day without a time component (date_trunc('day', p.created_at)::date), which allows for counting the unique users who posted for the first time on each day.
  • Counting: Using COUNT(DISTINCT p.user_id), we obtain the number of unique users who made their first post on each respective day.
  • Ordering: The results are sorted by the day in ascending order (ORDER BY day) to give a chronological overview of user engagement.

Final Output

The final report consists of two columns:

  • day: The date of user engagement without the time component.
  • new_contributors: The number of distinct users that made their first post on the forum for each day.

Example Results

day new_contributors
2023-12-15 16
2023-12-16 8
2023-12-17 7
2023-12-18 19
2023-12-19 15
2 Likes