How to retain value and compute based on group of first month purchase the next amount of purchase in sql

Question

i would like to create customer retention.

So i've this table :

id	date	purchase
1	2020-01	200
2	2020-12	100
2	2020-03	150
3	2020-03	200
1	2020-07	120
1	2020-08	300
3	2020-05	250

and i want this output :

purchase month	first purchase month	total
0	2020-01	200
6	2020-01	320
7	2020-01	620
0	2020-03	350
4	2020-03	600
11	2020-03	700

"Customers who placed their first order in Jan 2020 spent 200 in month 0 (so in Jan 2020) and 120 (320-200) in month 6 (so in Jul 2020)."

"Customers who placed their first order in Mar 2020 spent 350 in month 0 (so Mar 2020) and 250 (600-350) in month 4 (so May 2020)."

Thanks by advance for your help

Please keep in mind that `jQuery` and `Query` is not the same when you select tags — Carsten Løvbo Andersen, May 28 '21 at 07:31

score 0 · Accepted Answer · answered May 28 '21 at 08:56

You can easily do it with window function and common table expression.

Schema and insert statements:

 create table purchases(id int, date date, purchase int);
 insert into purchases values(1,    '2020-01',  200);
 insert into purchases values(2,    '2020-12',  100);
 insert into purchases values(2,    '2020-03',  150);
 insert into purchases values(3,    '2020-03',  200);
 insert into purchases values(1,    '2020-07',  120);
 insert into purchases values(1,    '2020-08',  300);
 insert into purchases values(3,    '2020-05',  250);

Query:

with cte as
     ( 
       select id,date,purchase,
       min(date)over(partition by id) FirstPurchaseMonth from purchases
     )
     ,cte2 as
     (
       select substr(date,6,2)-substr(firstpurchasemonth,6,2) Purchasemonth, max(FirstPurchaseMonth)firstpurchasemonth, 
       purchase,sum(purchase)total from cte
       group by firstpurchasemonth,substr(date,6,2)-substr(firstpurchasemonth,6,2) 
     )
     select purchasemonth,firstpurchasemonth,sum(total)over(partition by firstpurchasemonth order by purchasemonth)total
     from cte2

Output:

Purchasemonth	firstpurchasemonth	total
0	2020-01	200
6	2020-01	320
7	2020-01	620
0	2020-03	350
2	2020-03	600
9	2020-03	700

db<fiddle here

How to retain value and compute based on group of first month purchase the next amount of purchase in sql

1 Answers1