CODEWITHSUNDEEP
sqlpostgresql
Harish Shetty
Harish Shetty

Reputation: 64363

How to write sql to generate cumulative monthly sales per customer in Postgres

Given that I have a table called orders

orders

id
customer_id
created_at

How do I write a query to return the monthly cumulative order counts for each customer? I want to include the missing months in the series for Jan 2018 to May 2018

data

id   customer_id   created_at
1    200           01/20/2018
2    300           01/21/2018
3    200           01/22/2018
4    200           03/20/2018
5    300           03/20/2018
6    200           04/20/2018
7    200           04/20/2018

expected result

customer_id   month       count
200           01/01/2018  2
200           02/01/2018  2
200           03/01/2018  3
200           04/01/2018  5
200           05/01/2018  5

300           01/01/2018  1
300           02/01/2018  1
300           03/01/2018  2
300           04/01/2018  2
300           05/01/2018  2

I have a query to calculate the net cumulative count per month. I didn't have much success while converting the query to work for per customer cumulative counts.

WITH monthly_orders AS (
   SELECT date_trunc('month', orders.created_at) AS mon, 
          COUNT(orders.id) AS mon_count
   from   orders
   GROUP  BY 1
)
SELECT TO_CHAR(mon, 'YYYY-MM') AS mon_text,
       COALESCE(SUM(c.mon_count) OVER (ORDER BY c.mon), 0) AS running_count
FROM   generate_series('2018-01-01'::date, '2018-06-01'::date, interval '1 month') mon
LEFT JOIN monthly_orders c USING(mon)
ORDER  BY mon_text;

Upvotes: 1

Views: 321

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

If I understand correctly, you can just do:

select o.customer_id, date_trunc('month', o.created_at) AS mon, 
       count(*) AS mon_count,
       sum(count(*)) over (partition by o.customer_id
                           order by date_trunc('month', o.created_at) 
                          ) as running_count
from  orders o
group by o.customer_id, mon
order by o.customer_id, mon;

Upvotes: 1

Related Questions