Complex grouping of sales in PostgreSQL-CodePudding

I have a requirement to get or group the sales of particular products starting from the first day of the sales, so that to get a cohort analysis on the sales.I'm using Postgres DB ,so any help with this SQL or idea on how to achieve the expected result will be of great help.

Input

product    code   order_date  quantity 
Product_1  001    2020-01-02    30
Product_1  001    2020-01-03    31
Product_1  001    2020-02-23    55
Product_1  001    2020-04-30    125
Product_2  002    2020-03-20    110
Product_2  002    2020-04-03    59

Expected Output

product    code  quantity grouped_date   Month
Product_1  001      61      month_0      2020-01  
Product_1  001      55      month_1      2020-02
Product_1  001      0       month_2      2020-03
Product_1  001      125     month_3      2020-04
Product_2  002      110     month_0      2020-03
Product_2  002      59      month_1      2020-04

So from the above sample input , i would like to get the first sale date of each product and mark that month as month_0 and next month as month_1 and the next month as month_2 etc and sum up all the sales of those month to get a cohort analysis of products starting from the month of each products sale.

Below is the sample query which i used, but unable to add a grouping as it seems to be complicated for me.Any help will be much appreciated.

WITH base as
(
select product,code,order_date::DATE as order_date,quantity 
FROM sales
WHERE order_date::DATE BETWEEN '2020-01-01' AND CURRENT_DATE
)
,first_date_of_sales AS
(
SELECT product,code,MIN(order_date) as first_order_date FROM base
    GROUP BY product,code
)
SELECT * FROM first_date_of_sales

CodePudding user response：

Please try below mentioned query:

with cte as (
select product, code,
generate_series(min(date_trunc('month',order_date)), max(date_trunc('month',order_date)), interval '1 month') dt_series
from tab1 group by product, code
),
cte1 as(
select row_number() over (partition by product order by dt_series) sno, 
product, code, dt_series 
from cte
)

select 
cte1.product, 
cte1.code,
coalesce(sum(quantity),0) "quantity",
concat('month_',sno-1) "grouped_date", 
to_char(dt_series,'yyyy-mm') "Month" from cte1 
left join tab1 on cte1.dt_series=date_trunc('month', order_date) and cte1.product=tab1.product
group by cte1.product,cte1.code,dt_series,concat('month_',sno-1),to_char(dt_series,'yyyy-mm')
order by cte1.product,cte1.code,dt_series

DEMO