New user signups by month with running total

Community Building Data & reporting

AI-generated summary

aidanheerdegen shared an SQL query to calculate monthly user signups with a running total, using a CTE to group users by year-month and a window function (SUM OVER) to compute cumulative counts. They noted the OVER syntax was borrowed from Stack Overflow and mentioned it could be adapted for different date bins. JammyDodger pointed to a related topic with similar queries for comparison: weekly/monthly/yearly signups. aidanheerdegen appreciated the reference, admitting they hadn’t searched thoroughly enough and that it would have saved time.

1

Problem

For reporting purposes we need to know how many users have signed up, but grouped into useful date range intervals (bins).

We also need to know the total number of users in the same date bins.

Solution

The following query works by creating a common table expression (CTE) where the user.created_at column is transformed into a YEAR-MONTH string and a count of the number of users in that month “bin”.

Then a window operation (OVER) is used generate a cumulative sum of the count

WITH data as (
    SELECT 
        TO_CHAR(u.created_at,'yyyy-MM') AS yeardate, 
        COUNT(*) 
    FROM users u
    GROUP BY yeardate 
    ORDER BY yeardate
)
SELECT 
    yeardate,
    count,
    SUM(count::int) OVER (
        order by yeardate asc rows between unbounded preceding and current row
    ) AS cumulative
FROM data

The OVER incantation I copied from SO, so no idea if all those options are strictly required.

It would be easy to change the size of the date bins by changing how yeardate is formatted.

2

We’ve got some similar ones in this topic if you’d like to test them out and see how they compare:

3

Oh nice! Thanks.

I thought I’d searched, clearly not well enough or insufficient secret-sauce.

That would have saved me some time … sigh