Scenario: Streamhub's growth team uses a rolling 3-month user acquisition total to smooth seasonal variance.
Task: Write a query to return each signup month, the count of new users that month, and the rolling 3-month total — covering the current month and the two months immediately preceding it.
Assumptions:
- A signup month is identified by its first day and covers every user who registered within that month.
- A month's
monthly_signupsis the count ofuserswho registered in that month. - A month's
rolling_3month_signupsis the combinedmonthly_signupsacross the current month and the two months immediately preceding it; for the earliest two months in the data, the window contains fewer than three months and the total covers whatever months are available.
Output:
- One row per signup month present in the data.
- Columns in this order:
signup_month,monthly_signups,rolling_3month_signups. - 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 monthly_signups,
SUM(COUNT(*)) OVER (
ORDER BY
DATE_TRUNC('month', signed_up_at) ROWS BETWEEN 2 PRECEDING
AND CURRENT ROW
) AS rolling_3month_signups
FROM
users
GROUP BY
DATE_TRUNC('month', signed_up_at)
ORDER BY
signup_monthThe shape
A bounded window does the rolling-3-month work. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW defines a frame of exactly three monthly rows: the current month and the two before it. The window's SUM(COUNT(*)) adds those three monthly signup counts together. For the first two months in the data the frame has fewer than three rows because no prior rows exist; the sum still works and covers whatever is available.
Clause by clause
SELECT DATE_TRUNC('month', signed_up_at) AS signup_month, COUNT(*) AS monthly_signupsaggregates the user table to one row per signup month with that month's new-user count.SUM(COUNT(*)) OVER (ORDER BY DATE_TRUNC('month', signed_up_at) ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_3month_signupssums the monthly counts inside the rolling frame. The frame is positional: exactly the current row plus the two rows that come before it in month order.FROM users GROUP BY DATE_TRUNC('month', signed_up_at)aggregates to month so the window operates on monthly rows.ORDER BY signup_monthsorts the final output chronologically.
Why ROWS and not RANGE for the bounded window
After the GROUP BY, the data has one row per signup month, so ROWS BETWEEN 2 PRECEDING AND CURRENT ROW and a value-based RANGE frame agree row for row when every month is present. The two diverge if a month has zero signups and therefore no row in the result. ROWS would still grab exactly two prior rows regardless of how many calendar months back they sit. The prompt frames the rolling total as "the current month and the two months immediately preceding it" against the row set; ROWS matches that framing.
The trap
The earliest two months produce a rolling total over fewer than three rows. The frame is not undefined here. It simply contains whatever rows actually exist within 2 PRECEDING of the current row. The first month's frame is one row (itself); the second month's frame is two rows (itself plus the first). The rolling total for those rows equals the cumulative total at that point, which is the correct behavior — the window does not pad missing rows with zeros, it just sums what exists in the frame.
You practiced using a fixed-width preceding window (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) to smooth the per-month series — the window expands at the start and walks forward as the data grows.
