Customer Retention and Churn Analysis (Part 1/2) | SQL Interview Question Product Based Companies

[@sivasrimakurthi206]

wow, your logic is mind blowing. The way you dissect the problem statement and step by step approach to make everyone understand why you took a stand on any particular approach is very constructive and logical. Keep rocking @AnkitBansal. I can't say more, that I LOVE YOUR content.

[@ankitbansal6]

Thank you 😊

[@prashantmhatre9225]

Ankit Bhai ,
i have created below query -
select month , COUNT (case when month - prev_month = 1 then 1 end ) retention from
(
select cust_id , month(order_date) month , lag(month) over (partition by cust_id order by month ) prev_month from transactions
)
group by month;
This query works even if there are any duplicate data in the table.
OR below query is also giving same result.
select month , sum (diff) from (
select cust_id , month(order_date) month , lag(month) over (partition by cust_id order by month ) prev_month , case when month - prev_month = 1 then 1 else 0 end diff from transactions
)
group by month
in case we need to check for distinct cust_id , then use below one -
select month , count (distinct diff) from (
select cust_id , month(order_date) month , lag(month) over (partition by cust_id order by month ) prev_month , case when month - prev_month = 1 then cust_id end diff from transactions
)
group by month

[@exanode]

Genius!

[@goodisnotgoodenough_]

# Customer Retention (without JOINS)
WITH cte1 AS
(SELECT order_id,
cust_id,
order_Date,
EXTRACT(MONTH FROM order_date) AS mth,
DENSE_RANK() OVER(PARTITION BY cust_id ORDER BY EXTRACT(MONTH FROM order_date) ASC) AS rnk,
CASE WHEN DENSE_RANK() OVER(PARTITION BY cust_id ORDER BY EXTRACT(MONTH FROM order_date) ASC) = 1 THEN 0 ELSE 1 END AS ret_cnt
FROM transactions)
SELECT mth, SUM(ret_cnt) AS total_retention_count
FROM cte1
GROUP BY mth;

[@nitishsati6130]

You are unbelievable Ankit. Thank you so much for this.
Here's my solution to this -
select DATENAME(MONTH, order_date), sum(rnk) from (
select *, DENSE_RANK() over(partition by cust_id order by month(order_date))-1 as rnk
from transactionss) a
group by DATENAME(MONTH, order_date), MONTH(order_date)

[@MdRizwan-uu9dv]

🔮An alternate SQL Query to drive customer retention count using postgreSQL.
WITH cte as
(SELECT * , LAG(order_date) OVER(PARTITION BY cust_id order by order_date) as prev_order_date
from transactions )
SELECT EXTRACT(MONTH from order_date) as MONTHS ,
COUNT(prev_order_date from transactions ) ) as retented_customer_count
FROM CTE
GROUP BY EXTRACT(MONTH from order_date);
🔮An alternate query of customer churn count using postgreSQL
WITH cte as
(SELECT * , lead(order_date) OVER(PARTITION BY cust_id order by order_date) as next_order_date
from transactions)
SELECT EXTRACT(MONTH from order_date) as MONTHS ,
sum(CASE WHEN next_order_date is NULL then 1 else 0 end) as churn_customer_count FROM cte
WHERE next_order_date is NULL
GROUP by EXTRACT(MONTH from order_date);

[@prashantdhamunia]

my approach:-
select month(order_date) as mon,count(distinct case when month(order_date)=month(min_t) then cust_id end) as new_cust,
count(distinct case when month(order_date)>month(min_t) then cust_id end ) as repeat_c
from (
select *,min(order_date) over(partition by cust_id) as min_t from transactions
) a
group by 1

[@nivadonga6980]

My Solution in Postgres:
select extract(month from order_date) as Month, count(*) as no_of_customer,
sum(case when extract(month from order_date)-extract(month from prev_order_date)=1 then 1 else 0 end )
as no_of_repeat_customers from
(select *, lag(order_date,1,order_date) over(partition by cust_id) as prev_order_date from transactions) A
group by extract(month from order_date)

[@rahulmehla2014]

my approach:
with cte as(
select *,DATEDIFF(month,lag(order_date) over(partition by cust_id order by order_Date),order_date) as diff from transactions)
select month(order_Date) as month,sum(case when diff = 1 then 1 else 0 end) as cnt from cte group by MONTH(order_date)

[@sagarsaini5447]

with cte as(
select *
, ROW_NUMBER() over (partition by cust_id order by order_date) as rn
from transactions)
select MONTH(order_date) as month_date,
count(case when rn=2 then cust_id else null end) as last_mont_cust
from cte
group by MONTH(order_date)

[@aditya_pawar]

This will not work if customer order twice in same month, second order will be treated as repeat, but ask is retention over month

[@addhyasumitra90]

with CTE as (
Select *,
DATEDIFF(month, lag(order_date) OVER(Partition By cust_id order by order_date), order_date) as diff
from transactions1)
select MONTH(order_date) as month,
sum(case when DIFF=1 then 1 else 0 end) as cus_count
from CTE
group by MONTH(order_date)

[@biswanathprasad1332]

general solu by analytical method:
with cte as (select *,lag(order_date) over(partition by cust_id order by order_date) as prev_order from transactions )
select month(order_date) mnth,count(prev_order) tot_cust from cte group by month(order_date)

[@shekharraghuvanshi2267]

Thanks for the amazing solution..!!!
Below is my solution for the same.
with cte_retained as (
select month(order_date) as mnth
,case when rank() over(partition by cust_id order by order_date)=2 then cust_id else null end as retained_custid
from transactions15)
select mnth,count(retained_custid) from cte_retained group by mnth;

[@RohitKumar-zm3nw]

With CTE as (
Select order_id,cust_id,order_date, LEAD(order_date)over(partition by cust_id order by cust_id) AS D from transactions)
Select SUM(case when DATEDIFF(month,order_date,D)=1 then 1 end) from cte

[@shivammishra-mk9jp]

Hi Ankit
I did like this
with cte as ( select * , first_value(order_date) over(partition by cust_id) as first_order_date,
last_value(order_date) over(partition by cust_id) as last_order_date
from transactions ),
cte2 as ( select month(order_date) as months, sum(case when (order_date < first_order_date) then 1 else 0 end ) as l1,
sum(case when (order_date > first_order_date) then 1 else 0 end ) as l2
from cte
group by month(order_date) )
select c2.months,(c2.l1+c2.l2) as cx from cte2 c2
join cte2 c3 on c2.months = c3.months

[@sheebakhan9920]

with cte as (
select *, lag(order_date) over (partition by cust_id order by order_date) as prev_month
from transactions
)
select extract(month from order_date),sum(case when extract(month from order_date)-extract(month from prev_month) = 1 then 1 else 0 end) as count1
from cte
group by extract(month from order_date)

[@shahakunal1]

Hi @Ankit ,
select date_part('month',order_date),sum(case when date_diff=1 then 1 else 0 end) as returned_user from (select *,((date_part('year',order_date) - date_part('year',previous_purchase))*12)+(date_part('month',order_date) - date_part('month',previous_purchase)) as date_diff from (select *,lag(order_date) over (partition by cust_id order by order_date ) as previous_purchase from transactions) as a ) as g group by date_part('month',order_date);
this is another implementation

[@casseopiacodingcom7175]

with retention_map as (Select *,
count(1) over(partition by cust_id order by order_date rows between unbounded preceding and current row) as retention_map
from transactions )
select month( order_date) as month , sum( case when retention_map > 1 then 1 else 0 end) as retetion_count
from retention_map
group by month( order_date)

[@abhishekarora1]

I guess this won't work if customer ordered twice in the same month but not in the next month.

[@umakantkhandekar5748]

with c as(
select cust_id,order_date,lag(order_date) over(partition by cust_id order by order_date)pd
from transactions
)
select count(pd),month(order_date)
from c where datediff(month,pd,order_date)=1 or datediff(month,pd,order_date) is null
group by
month(order_date)

[@TechWithViresh]

select mm, count(case when cust_id=LG then cust_id end) from (select *, month(order_date) as mm,
lag(cust_id) over(partition by cust_id order by order_date) LG from transactions)t
group by mm;

[@rakeshpanigrahi577]

with cte as (
select *,
date_part('YEAR', order_date) + date_part('MONTH', order_date) as order_dt
from transactions
), cte1 as (
select *,
order_dt - (lag(order_dt, 1, order_dt) over(partition by cust_id order by order_dt)) as repeat_ind
from cte
)
select order_dt, sum(repeat_ind) as repeat_cust
from cte1
group by order_dt;

[@abhishekkukreja6735]

My Approach using Lag Function::
with cte as (
select * ,
lag(order_date) over(partition by cust_id order by order_date) as last_date
from transactions_ab
)
select datename(m , order_date) as Months,
sum(case when month(order_date) - month(last_date) = 1 then 1 else 0 end )
as xyz from cte
group by datename(m , order_date)
order by 2

[@GanesanDinesh]

Hi Ankit,
Thanks for your videos
;with cte_1 as
(
select *,lag(month(order_date)) over (partition by cust_id order by month(order_date)) A from leetcode_transactions
)
select month (order_date) month ,count(case when A= 1 then cust_id end ) as total_count from cte_1
group by month (order_date)

[@sivasrimakurthi206]

wow, this is nice quick way to find answer to this problem statement, thanks for sharing this Dinesh 👏

[@new_moon_12]

Really nice piece of code

[@RoshankumarPatro-v9x]

with ct as(
select *,
lag(order_date) over (partition by cust_id order by order_date) as next_purchase
from transactions
)
select month(order_date) as month,
sum(case when DATEDIFF(month, next_purchase, order_date) >= 1 then 1 else 0 end) customer_retention
from ct
group by month(order_date)

[@srinivashosur9964]

with CTE as
(select month(order_date) as month
,month(order_date)-month(coalesce(lag(order_date) over(partition by cust_id order by cust_id), order_date)) as prev_month
from transactions)
select month, sum(prev_month) as retained_cust
from CTE
group by month

[@mananagrawal4114]

with cte as(
SELECT datepart(month, order_date) as month,
case when lag(cust_id) over (Partition by cust_id order by order_date) = cust_id then 1 else 0 end as retention
from transactions
)
SELECT month, sum(retention) as retentions
from cte
GROUP by month

[@mohdtoufique7446]

Hi Ankit.. Thanks for the content...
An approach using window functions
WITH CTE AS(
SELECT *,DATEPART(month,order_date)AS month_ordered,DATENAME(month,order_date) AS month_name
FROM transactions),
CTE_2 AS(
SELECT cust_id,month_ordered,LAG(month_ordered)OVER(PARTITION BY cust_id order by month_ordered) AS prev_month
FROM CTE
GROUP BY cust_id,month_ordered)
SELECT month_ordered,SUM(CASE WHEN (month_ordered-1)=prev_month THEN 1 ELSE 0 END) AS no_retained_customers
FROM CTE_2
GROUP BY month_ordered

[@ankitbansal6]

If a customer is placing multiple orders then this might count same customer multiple times. With a small tweak this can be fixed..

[@mohdtoufique7446]

@@ankitbansal6 .. Thanks for pointing out

[@santanuroy2121]

hello Ankit, just for my clarification from this video to ask you one question that let say one customer ordered december 21 and january 22 and how do we calculate by datediff month function in that case we never get 1

[@architgarg97288]

Hi Sir
with temp1 as(
select * , lag(order_date,1,order_date) over (partition by cust_id order by order_date) as lastdate from transactions
)
select month(order_date), sum(case when month(order_date)-month(lastdate)=1 then 1 else 0 end) as custtt from temp1 group by month(order_date)

[@vijaygupta7059]

with cte as
(
Select *
,case when cust_id in (Select cust_id from transactions where datepart(month,t.order_date)>datepart(month,order_date) ) then 'reten_customer' else 'customer_chun' end as new
from transactions as t
)
Select datepart(month,order_date) as month_number,sum(case when new='reten_customer' then 1 else 0 end) as cnt
from cte
group by datepart(month,order_date)

[@chenchuladileep7410]

Sir Your videos are top notch. The way you approach the problem is very great. When i try to start few problems i get stuck in 1st step itself ,after watching few minutes of the video im able to do it then. How to develop the skill to approach the problem sir. Give me some tips please

[@ankitbansal6]

Just keep solving more problems

[@sriharit.k8973]

with cte as (
select *,month(order_date) as curr_month, month(lag(order_date) over(partition by cust_id order by order_date)) as prev_month
from transactions
)
select curr_month as month, count( prev_month) as customers_retained from cte
group by curr_month;

[@redwannabil8031]

i entered multiple orders of a customer in the same month and run the below query...it gives the same rseult
select month(order_date) as months,
sum(case when datediff(month,prev,order_date) >=1 then 1 else 0 end) as cnt from
(select cust_id,order_date,lag(order_date) over (partition by cust_id order by cust_id) as prev from transactions) x
group by month(order_date)

[@animeshanand5898]

with master as(
select *,lag(order_date,1,order_date) over (partition by cust_id order by order_date) as prv_ord from transactions_new)
select month(order_date) as month_name,count(case when datediff(month,prv_ord,order_date)=1 then cust_id end) as cust_id_count
from master
group BY
month(order_date)

[@mohammadabdullahansari6314]

Hi Ankit, this video was hard to follow as I think no of likes to views ratio shows that.
My query to find % of retained customers:
select
month(t1.order_date),
(count(case when month(t1.order_date) - month(t2.order_date) = 1 then t2.cust_id else null end) * 100 /
count(distinct t1.cust_id)) as one_month_retention
from
transactions t1
join
transactions t2 on t1.cust_id = t2.cust_id
group by 1
order by 1

[@ankitbansal6]

Thanks for posting 👏

[@GautamKumar-ci4rz]

solution in oracle DB
with cte as
(
select
cust_id,
order_date,
lag(order_date)over(partition by cust_id order by order_date) lag_date
from transactions
)
select
to_char(order_date,'MON'),
sum(case when lag_date is null then 0 else 1 end) as no_of_user_retention
from cte
group by to_char(order_date,'MON')
;

[@adhiththyagarajan6146]

This is my solution using Window functions:
SELECT
DISTINCT Months
,SUM(rt_cust)
FROM
(SELECT cust_id
,order_date
,MONTH(order_date) AS Months
,CASE WHEN MONTH(order_date) - MONTH(LAG(order_date) OVER (PARTITION BY cust_id ORDER BY order_date)) = 1
AND rank() OVER(PARTITION BY cust_id ORDER BY order_date) = 2 THEN COUNT(cust_id) ELSE 0 END AS rt_cust
FROM transactions
GROUP BY cust_id, order_date) RT
GROUP BY Months
;

[@ramchavali5825]

select mt2.month_order_date,
sum(case when mt1.cust_id = mt2.cust_id and mt1.month_order_date + interval '1 month' = mt2.month_order_date then 1 else 0 end) as repeated_cust
from
monthly_transactions mt1,
monthly_transactions mt2
group by mt2.month_order_date

[@anish_bhateja]

with orders as (select *,min(order_date) over(partition by cust_id) as first_order from zomato_transactions order by cust_id),final as
(select *, case when order_date=first_order then 'New' when month(order_date)-month(first_order) = 1
then 'Retained' else 'Not Retained' end as customer_retention,month(order_date) as month from orders order by month)
select month,count(case when customer_retention='Retained' then 1 end) as customers_retained from final group by 1;

[@khushboobansal2312]

I have solved this using row_number: with cte as (
Select *,Extract(Month from order_date) as Month,
row_number() over (partition by transactions.cust_id order by Extract(Month from order_date)) as rn
from transactions)
Select month,flag,count(1) from (
Select *,case when rn = 1 then 'New' else 'Repeat' end as flag
from cte)
group by month,flag

[@arthurmorgan9010]

sir please review my solution:
-- monthly matrix
with cte as
(
select 'FEB' as month,count(a.cust_id) as retained from transactions a
join transactions b
on a.cust_id = b.cust_id and a.order_date < b.order_date
)
select 'jan' as month,count(c.cust_id) as retained from transactions c
join transactions d
on c.cust_id = d.cust_id
where c.order_date < '2020-02-01'
and d.order_date < '2020-01-01'
union all
select * from cte

[@ShivaRam-k8v]

select month, sum(diff) from
(select month(order_date) as month,cust_id
,lag(order_date,1,order_date) over(partition by cust_id order by month(order_date)) as Pervs
,case when order_date=lag(order_date,1,order_date) over(partition by cust_id order by month(order_date)) then 0 else 1 end as diff
from transactions)A
group by month

[@RishabhAggarwal-u7v]

What if we have to calculate MOM for next 5-6 months

[@harishkanta3711]

Ankit bhai please let me know if am right here:
with cte as (select cust_id,order_date,lag(order_date) over(partition by cust_id order by order_date) as lag_date
from transactions)
select month(order_date),sum(case when lag_date is not null then 1 else 0 end) as no_retention_count
from cte group by month(order_date)

[@parth_pm16]

This is my solution and I got stuck for month 1 which has "zero count" but I got help from one comment
with cte as
(select
*
, count(1) over(partition by cust_id order by order_date) - rank() over(partition by cust_id order by cust_id) as repeat_customer
from transactions)
select
month(order_date) month_number
, count(case when repeat_customer = 1 then cust_id end) as num_of_repeat_cust
from cte
group by month(order_date);

[@tusharsharma8750]

Hi Ankit,
Just a suggestion, Please also make videos for the same questions by solving using pandas.

[@ankitbansal6]

Sure

[@rajeshgoud9619]

here one doubt how the difference for month between will be calculate each month will be deducted with the repective month only right how come feb -jan will happen here?

[@pallavdubey007]

using analytical function on oracle sql
with prev_date as (select cust_id ci, order_date od,lag(order_date,1) over(partition by cust_id order by order_date)prev from transactions )
select to_char(prev_date.od,'Month') Mon,count(case when prev is not null then ci else null end) tot from prev_date
group by to_char(prev_date.od,'Month');

[@ankitbansal6]

Customer can place multiple orders in same month.

[@LiveWithDebasmita]

Hi Ankit Sir,
my question to you is: if the same customer returns back in the month of April after Feb so the output should be
01 0
02 3
04 0
and the same customer has an entry in the month of May then the output would look like:
01 0
02 3
04 0
05 1
Is this correct?
so my query is below :
with cust_check as (
select cust_id , next_order_date , previous_order_date, case when previous_order_date = 0 then 0
when previous_order_date != 0 then period_diff(date_format(next_order_date, "%Y%m") , date_format(previous_order_date,"%Y%m"))
end as month_diff from
(
select cust_id , order_date as next_order_date , lag(order_date , 1 , 0)
over(partition by cust_id order by cust_id) as previous_order_date from
(select * from transactions order by cust_id , order_date) as a
) as a
) ,
final as (
select a.cust_id , year(a.next_order_date) as year_of_date , month(a.next_order_date) as month_of_date ,
a.previous_order_date , a.month_diff
from cust_check as a inner join
(select cust_id , count(cust_id) as c from cust_check group by cust_id having count(cust_id) > 1) as b
on a.cust_id = b.cust_id and a.month_diff in (0,1)
)
select year_of_date , month_of_date , sum(month_diff) as no_of_returning from final
group by year_of_date , month_of_date;

[@ankitbansal6]

You are right. Thanks for posting 👏

[@LiveWithDebasmita]

@@ankitbansal6 Sir, does this query look good to you?

[@ahsan_habib_sunny]

@@LiveWithDebasmita hello, I've checked your question and written my own query. can you please check on your device whether the results match or not. Thanks in advance.
with t1 as
(select cust_id,order_date,lead(order_date) over(partition by cust_id order by order_date) as next_order_date,
date_part('month',lead(order_date) over(partition by cust_id order by order_date)) - date_part('month',order_date) as diff_between_orders
from transactions)
select date_part('month',next_order_date) as month,
coalesce(sum(case when diff_between_orders = 1 then 1
when diff_between_orders = 0 then 0 end),0) as number_retention
from t1
where diff_between_orders is not null
group by 1
order by 1 asc;

[@sreejitchakraborty6575]

Hi Ankit, Is this correct?
with cte as(
select q.*,lag(month) over(partition by cust_id order by month) as lagged from
(
select distinct cust_id,
strftime('%m', order_date) AS "Month"
from transactions t
)q
),retained as(
select month,count(*) from cte where month-lagged=1
group by month
),no as(

select distinct strftime('%m', order_date) AS "Month",0 from
transactions t
where strftime('%m', order_date) not in (select distinct month from retained)
)
select * from retained
union
select * from no

[@SS-lt1nf]

My soln using window function. Pls let me know if this can be improved.
with cte as(
select order_date,
lag(order_date) over(partition by cust_id order by order_date) prev_ord_dt,
date_trunc('month',lag(order_date) over(partition by cust_id order by order_date)) as prev_month,
case when date_trunc('month', order_date) - interval '1 month' = date_trunc('month',lag(order_date) over(partition by cust_id order by order_date)) then 1 else 0 end as retn
from transactions)
select to_char(order_date,'yyymm'),
sum(retn)
from cte
group by to_char(order_date,'yyymm');

[@ankitbansal6]

Thanks for posting

[@kumz202]

SELECT month(order_date) AS month_date,
sum(CASE
WHEN lag_date IS NULL THEN 0
ELSE 1
END) AS customer_retention
FROM
(SELECT cust_id,
order_date,
lag(order_date) over(PARTITION BY cust_id
ORDER BY order_date) AS lag_date
FROM transactions)a
GROUP BY month(order_date)

[@taniyasaini6830]

with cte as (
select *,month(order_date) as m, lag(month(order_date)) over (partition by cust_id order by month(order_date)) as lag_val from transactions
)
select count(cust_id),m from cte where lag_val is not NULL group by m;

[@sahilummat8555]

;with cte as (
select order_id,cust_id,MONTH(order_date)as tm,amount,
month(lag(order_date)over(partition by cust_id order by month(order_date)) )as lm ,
case when MONTH(order_date)-month(lag(order_date)over(partition by cust_id order by month(order_date))) =1 then cust_id end as customer
from transactions)
select tm,count(distinct customer) from cte
group by tm

[@apurvasaraf5828]

select m,sum(case when order_date>j then 1 else 0 end) as c
from
(select month(order_date)as m,order_date,min(order_date)
over(partition by cust_id order by order_date) as j
from transactions)p
group by m

[@rawat7203]

Sir kindly review this solution on mysql
with cte as(
select *, lag(order_date) over (partition by cust_id order by order_date) as prev_month from transactions)
select monthname(order_date) as month,
sum(case when month(date_add(prev_month, interval 1 month)) = month(order_date) then 1 else 0 end) as total_retain
from cte
group by monthname(order_date);

[@shivammadaan9498]

this is my analytical solution, it works for this one but i am not sure if it works for all the cases.
with cte as (
select *, month(order_date) as months, lag(month(order_date),1,month(order_date))over(partition by cust_id order by order_date) as last_month
-- , month(order_date)-lag(month(order_date))over(partition by cust_id) as month_diff
from transactions)
select months, sum(case when months - last_month =1 then 1 else 0 end) as retained
from cte
group by months;

[@Dhanushts-g7x]

with cte1 as
(select cust_id,order_date,lag(order_date) over(partition by cust_id order by order_date) lst_mnth
from transactions)
,cte2 as
(select substr(monthname(order_date),1,3) month,count(*) no_retended from cte1 where
month(order_date)=month(lst_mnth)+1 group by month
union all
select substr(monthname(order_date),1,3),0 from transactions)
select month,max(no_retended) no_retended from cte2 group by month
order by month desc
(my simple ans)

[@exanode]

Oracle Solution :
WITH RETENTIONS AS (
SELECT
TO_CHAR(ORDER_DATE, 'MM/YYYY') AS MONTH_YEAR,
LAG(ORDER_DATE) OVER (PARTITION BY CUST_ID ORDER BY ORDER_DATE) AS PREV_MONTH,
CUST_ID
FROM
TRANSACTIONS
)
SELECT
MONTH_YEAR,
COUNT(DISTINCT CASE WHEN PREV_MONTH IS NOT NULL THEN CUST_ID ELSE NULL END) AS RETAINED
FROM
RETENTIONS
GROUP BY
MONTH_YEAR
ORDER BY
1,2;

[@prakharsrivastava6571]

WITH t1 AS (
SELECT
*,
LAG(order_date) OVER (PARTITION BY cust_id ORDER BY order_date) AS previous_order_date
FROM
transactions
),
t2 AS (
SELECT
*,
CASE
WHEN previous_order_date IS NOT NULL
AND EXTRACT(YEAR FROM order_date) = EXTRACT(YEAR FROM previous_order_date)
AND EXTRACT(MONTH FROM order_date) = EXTRACT(MONTH FROM previous_order_date) + 1
THEN 'retained_customer'
ELSE 'not_retained_customer'
END AS retention
FROM
t1
),
t3 AS (
SELECT
EXTRACT(MONTH FROM order_date) AS month,
retention
FROM
t2
WHERE
retention = 'retained_customer'
)
SELECT
month,
COUNT(*) AS total_retained_customer
FROM
t3
GROUP BY
month
ORDER BY
month;

[@sherona7844]

Will it work for unbounded retention also?

[@harshadam9215]

How do I get the summary of the data along with this result

[@akelakela5524]

WITH
prev AS
(
SELECT *,LAG(order_date,1, order_date) OVER(partition by cust_id order by order_date) as prev_date FROM transactions
)
,
complete AS
(
SELECT EXTRACT(MONTH FROM order_date) as month, SUM(CASE WHEN EXTRACT(MONTH FROM order_date) - EXTRACT(MONTH FROM prev_date) =1 then 1 else 0 end) AS retention FROM prev
GROUP BY EXTRACT(MONTH FROM order_date)
)
SELECT * FROM complete

[@gowdaharish3370]

Hi,how to extract month from datediff in MySQL?

[@ankitbansal6]

Check this out
stackoverflow.com/questions/288984/the-difference-in-months-between-dates-in-mysql

[@tupaiadhikari]

Awesome Video.

[@sz6618]

;with cte as (
select *,
datediff(month,order_date,lag(order_date) over (partition by cust_id order by order_date))
as rn,
datediff(month,order_date,lead(order_date) over (partition by cust_id order by order_date))
as rn2 from #transactions )
select month(order_date),
sum(case when rn=-1 then 1 else 0 end) as cnt
from cte
where rn=-1 or rn2 =1
group by month(order_date)

[@harshit_dataanalyst]

WITH CTE AS
(
SELECT cust_id,order_date,LAG(order_date,1) OVER( PARTITION BY Cust_id ORDER BY order_date) Previous_date FROM transactions
)
SELECT count( CASE WHEN DATEDIFF(MONTH,order_date,Previous_date)=-1 THEN cust_id END) AS "Retain_cust" FROM CTE
WHERE Previous_date is not null

[@subhojitchatterjee6312]

MY SQL Solution:
SELECT MONTH_NAME,SUM(CASE WHEN COUNTER=ORDER_DATE THEN 0 ELSE 1 END) AS NUM_RETAINED FROM(
SELECT *,MONTHNAME(ORDER_DATE) AS MONTH_NAME,LAG(ORDER_DATE,1,ORDER_DATE) OVER(PARTITION BY CUST_ID) AS COUNTER FROM TRANSACTIONS) T1
GROUP BY 1;

[@edunomicspvt4987]

a left join b and b right join a : same output or not.....need some clarification on this question

[@ankitbansal6]

Absolutely same. You can always switch between left and right join by switching tables

[@kanchidoshi6907]

select to_char(order_date,'YYYY-Mon') as mon,
sum(case when retain_cust =0 then 0 else 1 end) as retained_cust
from
(select order_date,order_id,cust_id,
coalesce(lag(cust_id) over(partition by cust_id order by order_date),0) as retain_cust
from transactions)x
group by mon

[@ankitbansal6]

You need to check if customer ordered last month as well. There can be multiple orders in current month

[@NatureAndTravelAndAnimals]

Can you suggest any platform or website practice leet code problems.

[@ankitbansal6]

On leetcode itself you can practice.

[@divyanshsharma7406]

Hi Ankit Sir
Can you also upload a document containing the problems and solutions to all your videos?

[@ankitbansal6]

Hi Divansh, It's a good suggestion. I will plan the same in near future.

[@VishalSharma-hv6ks]

Productive video..

[@rajunaik8803]

*need to filter out 0 retained customers for jan*
my enhanced SQL:
with cte1 as(
select cust_id,min(order_date) prev_mon,max(order_date) next_mon from transactions group by cust_id
),cte2 as (
select cust_id,prev_mon,next_mon,DATEDIFF(MONTH,prev_mon,next_mon) mon_diff from cte1
)
select DATEPART(month,next_mon) as month,count(cust_id) no_of_retained_customers from cte2 where mon_diff!=0
group by DATEPART(month,next_mon)

[@rachit.gulyani]

WITH CTE AS
(SELECT *,LAG(ORDER_DATE) OVER (partition BY cust_id ORDER BY order_id) AS PREVIOUS_ORDER_DATE
FROM transactions
)
SELECT MONTH(ORDER_DATE) AS MONTH,COUNT(DISTINCT CASE WHEN ABS(DATEDIFF(MONTH,ORDER_DATE,PREVIOUS_ORDER_DATE))=1 THEN cust_id END) AS NO_OF_CUSTOMER_RETAINED
FROM CTE
GROUP BY MONTH(ORDER_DATE)
ORDER BY MONTH;

[@ganeshkumar1424]

Your videos are very good and addicted . I become a big fan of yours video . Can you please post video for python as well .

[@ankitbansal6]

Thank you 😊. In Python what are you looking for ? Basics or pandas numpy ?

[@ganeshkumar1424]

@@ankitbansal6 pandas numpy

[@ankitbansal6]

@@ganeshkumar1424 ok will plan

[@hiteshshettyk]

Do let me know if iam missing out on anything.
with rws as (
select t.*,
lag ( amount ) over (
partition by cust_id
order by order_date
) prev_month_amount
from transactions t
)
select count (*) total_custs_in_month,
count ( prev_month_amount ) num_with_trans_prev_month
from rws r
where order_date >= date'2020-02-01';

[@ankitbansal6]

Customer can have multiple orders in same month. We need to make sure orders were placed in this and last month both

[@venkatgopi1841]

thanks for the video

[@ankitbansal6]

Welcome

[@hiralalpatra500]

with cte as(select *,ROW_NUMBER() over(partition by cust_id order by order_date) as row_
from transactions)
, cte2 AS(select *,case when lead(row_) over(partition by cust_id order by row_)-row_=1 then 1 else 0 end as ret_cust from cte)
select cust_id from cte2
where ret_cust=1

[@ahsan_habib_sunny]

please if you see my comment, then let me know if I write the below query whether it is fine or not. Yours and my output are identical.
with t1 as
(select cust_id,order_date,lag(order_date) over(partition by cust_id) as sec_order_date from transactions)
select date_part('month',order_date) as month, coalesce(count(sec_order_date),0) as no_retention from t1 group by 1;

[@ankitbansal6]

If the customer is coming multiple times in a month then there will an issue

[@ahsan_habib_sunny]

@@ankitbansal6
with t1 as
(select order_id,cust_id, date_part('month',order_date) as first_order_month,
lag(date_part('month',order_date)) over (partition by cust_id order by order_date asc),
date_part('month',order_date) - lag(date_part('month',order_date)) over (partition by cust_id order by order_date asc) as diff
from transactions)
select first_order_month,
sum(case when diff = 1 then 1
when diff is null then 0 end) as no_retention
from t1
group by first_order_month
order by first_order_month asc

[@manjunathk4539]

Hello Ankit,
I was not sure how to get the record count 0 so i used UNION ALL as a hack. Please let me know what you think
WITH retention AS (
select
cust_id
,order_date
,CASE
WHEN date_part('month',order_date) - date_part('month', LAG(order_date) OVER(partition by cust_id order by order_id)) = 1
THEN 1 ELSE 0 END as month_diff
from
transactions
)
select
date_part('month',order_date) AS month
,COUNT(*) AS retention_COUNT
from retention where month_diff = 1
group by date_part('month',order_date)
UNION ALL
select date_part('month', min(order_date)),0 AS count from transactions;
I just tweaked my query and I am able to filter records without using UNION ALL
WITH retention AS (
select
cust_id
,order_date
,CASE
WHEN date_part('month',order_date) - date_part('month', LAG(order_date) OVER(partition by cust_id order by order_id)) = 1
THEN 1 ELSE 0 END as month_diff
from
transactions
)
select
date_part('month',order_date) AS month
,SUM(CASE WHEN month_diff != 0 THEN 1 ELSE 0 END) AS retention_COUNT
from
retention
group by date_part('month',order_date)

[@anupamsharma4263]

[with transactions as (select 1 as order_id,
1 as cust_id,
'2020-01-15' as order_date,
150 as amount
union all
select 2 as order_id,
1 as cust_id,
'2020-02-10' as order_date,
150 as amount
union all
select 3 as order_id,
1 as cust_id,
'2020-03-10' as order_date,
150 as amount
union all
select 3 as order_id,
2 as cust_id,
'2020-01-16' as order_date,
150 as amount
union all
select 4 as order_id,
2 as cust_id,
'2020-02-25' as order_date,
150 as amount
union all
select 5 as order_id,
3 as cust_id,
'2020-01-10' as order_date,
150 as amount
union all
select 6 as order_id,
3 as cust_id,
'2020-02-20' as order_date,
150 as amount
union all
select 7 as order_id,
4 as cust_id,
'2020-01-20' as order_date,
150 as amount
union all
select 8 as order_id,
5 as cust_id,
'2020-02-20' as order_date,
150 as amount
union all
select 9 as order_id,
5 as cust_id,
'2020-03-20' as order_date,
150 as amount),
logic as (select cust_id,
month(order_date) month,
case when datediff(month, min(order_date) over(partition by cust_id), order_date) > 0 then 1
else 0
end as month_diff
from transactions),
month_wise_data as (select *,
sum(month_diff) over(partition by month) as number_of_retention_cust
from logic)
select distinct month,
number_of_retention_cust
from month_wise_data];

[@ashishkumarjha2587]

#DAY_7
If you are using MYSQLWorkbehch
WITH cte AS (
SELECT
*
,ROUND(DATEDIFF( LAG(order_date,1,order_date) OVER(PARTITION BY cust_id ORDER BY order_date),order_date)/30) diff
FROM transactions
)
SELECT
MONTH(c1.order_date) AS month_num , COUNT(DISTINCT c2.cust_id) tota_count
FROM cte c1
LEFT JOIN cte c2 ON c1.cust_id = c2.cust_id AND c1.diff = -1
GROUP BY MONTH(c1.order_date)