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 ofYYYY-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 asp
) is joined with the subquery result (aliased asfp
) onuser_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’screated_at
timestamp with thefirst_post_date
from the subquery to ensure we’re only dealing with first posts, and it checks that thecreated_at
timestamp falls within the specified date range, inclusive of theend_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 |