CODEWITHSUNDEEP
sqlpostgresqldategroup-bycount
nightrider
nightrider

Reputation: 13

How do I add a running total of users alongside the new users added by day?

I am trying to show the new users added today, alongside the total count.

This 

SELECT
DATE("CreatedAt"),
COUNT(DATE("CreatedAt")),
COUNT(DISTINCT"Id")

FROM "User"

GROUP BY DATE("CreatedAt")
ORDER BY DATE("CreatedAt") DESC

Outputs this

Date.         New  Total
"2020-04-21"    50  50
"2020-04-20"    40  40
"2020-04-19"    30  30
"2020-04-18"    20  20
"2020-04-17"    10  10

How do I display the aggregate number of users (to date) on the Total column? The 21st should read 150.

Upvotes: 1

Views: 22

Answers (1)

GMB
GMB

Reputation: 222482

You can use window functions for this: basically, you want a window sum of the count of rows per group, like:

select
    date("CreatedAt") created_day,
    count(*) "New",
    sum(count(*)) over(order by date("CreatedAt")) "Total"
from "User"
group by date("CreatedAt")
order by created_day desc

Upvotes: 1

Related Questions