Dashboard Report - New Contributors

Community Building Data & reporting
, ,

AI-generated summary

SaraDev provides an SQL query for a Dashboard Report - New Contributors, designed to track the number of new users who made their first post each day within a specified date range. The query uses parameters :start_date and :end_date to define the timeframe and identifies first-time contributors by joining the posts table with a subquery that finds each user’s earliest post (first_post_date). It filters for posts that are not deleted and ensures only first posts are counted by matching created_at with first_post_date. The results are grouped by day, counting distinct users (new_contributors) and ordered chronologically. The output includes the date and the count of new contributors, useful for monitoring user engagement over time.

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
Admin dashboard report reference guide