Find repeat customers after making their transcation in a month

astroboy 0
Viewed 42

I've a sample data - data1

Date       User     Orderid
12-02-2020  A        50274
13-02-2020  B        34704
18-02-2020  A        12079 
01-03-2020  C        69711
13-03-2020  B        36813
01-04-2020  D        57321

Customer A made the first transaction in Feb and another transaction in same month. Customer B made the first transaction in Feb and made a transaction again in March.

How can I identify the customer acquisation in a month and their following months orders?

month | customers_acquired | made_transcation_in_month+1 | made_transaction_in_month+2
  2          2                      1                               0
  3          1                      0                               0
  4          1                      0                               0

In the above result, In month 2, two customers made their first transcations and one of them made again in next month.

In month 3, one new customer made a transcation and never made any transactions again. Same goes with month 4.

sql
1 Answers
select   year(date)                   as "year"
        ,month(Date)                  as "month"
        ,count(new_customer_cnt)      as customers_acquired
        ,count("repeat_customers+1")  as "made_transcation_in_month+1"
        ,count("repeat_customers+2")  as "made_transcation_in_month+2"
from     (
         select * 
                ,case when "User" <> lag("User") over(order by "User", Date) or lag("User") over(order by "User", Date) is null then 1 end                                                         as new_customer_cnt
                ,case when "User" = lead("User") over(partition by "User" order by Date) and month(dateadd(month, 1, date)) = lead(month(date)) over(partition by "User" order by Date) then 1 end as "repeat_customers+1"
                ,case when "User" = lead("User") over(partition by "User" order by Date) and month(dateadd(month, 2, date)) = lead(month(date)) over(partition by "User" order by Date) then 1 end as "repeat_customers+2"
         from   t 
         ) t
group by year(date), month(Date)
year month customers_acquired made_transcation_in_month+1 made_transcation_in_month+2
2020 2 2 1 0
2020 3 1 0 0
2020 4 1 0 0

Fiddle

DannySlor0
Related