# 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:** 1\
**Showing post:** 1

<div class="post-metadata">

**Author:** ![aidanheerdegen](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/aidanheerdegen/32/277061_2.png) [@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")

</div>

# 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.

---

_[View the full topic](https://meta.discourse.org/t/new-user-signups-by-month-with-running-total/292073)._