Scenario: Streamhub's growth team is tracking cumulative user acquisition and needs each signup month alongside that month's new users and the running total through that month.
Task: Write a query to return each calendar month in which users signed up, the count of new users that month, and the running total of all users signed up from the earliest signup month through that month.
Assumptions:
- A signup month is identified by its first day and covers every user who registered within that month.
- A month's
new_usersis the count ofuserswho registered in that month. - A month's
cumulative_usersis the combined count ofuserswho registered from the earliest signup month through that month inclusive.
Output:
- One row per signup month present in the data.
- Columns in this order:
signup_month,new_users,cumulative_users. - Sorted by
signup_monthascending.
Schema · analytics 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
DATE_TRUNC('month', signed_up_at) AS signup_month,
COUNT(*) AS new_users,
SUM(COUNT(*)) OVER (
ORDER BY
DATE_TRUNC('month', signed_up_at) ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS cumulative_users
FROM
users
GROUP BY
DATE_TRUNC('month', signed_up_at)
ORDER BY
signup_monthThe shape
Group signups into months first, then accumulate the monthly counts. DATE_TRUNC('month', signed_up_at) puts every signup into a month bucket, COUNT(*) gives that month's new user count, and SUM(COUNT(*)) OVER (ORDER BY ...) runs forward through the months adding each month's new users to the total carried in from prior months.
Clause by clause
SELECT DATE_TRUNC('month', signed_up_at) AS signup_month, COUNT(*) AS new_usersproduces one row per signup month and counts the users who registered in that month. Every signup on any day in January 2022 truncates to2022-01-01and lands in the same group.SUM(COUNT(*)) OVER (ORDER BY DATE_TRUNC('month', signed_up_at) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_usersaccumulates the per-month counts in month order.UNBOUNDED PRECEDINGanchors the frame at the earliest signup month;CURRENT ROWextends it through the current month, so each row carries every prior month's new users plus its own.FROM users GROUP BY DATE_TRUNC('month', signed_up_at)aggregates the user table to one row per month, which is the row set the window function then accumulates across.ORDER BY signup_monthsorts the final output chronologically.
Why SUM(COUNT(*)) and not COUNT(*) inside the window
The inner COUNT(*) is the group aggregate that produces new_users for each month. The outer SUM(...) is the window aggregate that runs forward across those monthly counts. Both are needed because the window function operates on the post-GROUP BY row set: at that stage, each row is one month and the per-month count has already been computed by COUNT(*). The window's job is to add those counts up.
You practiced building cumulative growth over per-month signup counts, with each month carrying both its own arrivals and the all-time total through that point.
