# New user signups by month with running total
**URL:** https://meta.discourse.org/t/new-user-signups-by-month-with-running-total/292073
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [January 18, 2024, 1:18pm UTC](https://meta.discourse.org/t/new-user-signups-by-month-with-running-total/292073 "2024-01-18T13:18:40Z")
**Posts on this page:** 3
**Page:** 1
### Author:  [@aidanheerdegen](https://meta.discourse.org/u/aidanheerdegen)
#### Post date: [January 18, 2024, 1:18pm UTC](https://meta.discourse.org/t/new-user-signups-by-month-with-running-total/292073/1 "2024-01-18T13:18:40Z")
# 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`
```sql
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.
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [January 18, 2024, 1:30pm UTC](https://meta.discourse.org/t/new-user-signups-by-month-with-running-total/292073/2 "2024-01-18T13:30:44Z")
We’ve got some similar ones in this topic if you’d like to test them out and see how they compare:
> [@Weekly/Monthly/Yearly Signups](https://meta.discourse.org/t/weekly-monthly-yearly-signups/278119):
>
> memo These queries give a breakdown of how many new user accounts have been created within the timeframe, excluding staged and non-activated accounts. The totals are broken down by number of signups each week/month/year, a running total for the time period selected, and a total number to date which includes all previous signups. NB: If a user is deleted their records no longer appear in the database and will therefore no longer be included in the query results. This can lead to variations wh…
---
### Author:  [@aidanheerdegen](https://meta.discourse.org/u/aidanheerdegen)
#### Post date: [January 18, 2024, 1:43pm UTC](https://meta.discourse.org/t/new-user-signups-by-month-with-running-total/292073/3 "2024-01-18T13:43:50Z")
> [@JammyDodger](#):
>
> We’ve got some similar ones in this topic if you’d like to test them out and see how they compare
Oh nice! Thanks.
I thought I’d searched, clearly not well enough or insufficient secret-sauce.
That would have saved me some time … _sigh_