SQL Interview Question on Customer Retention in Subscription Model | English | Swiggy | sqlonline.in

SQL Query for retention

In this SQL interview question, we'll look at how to find the month-on-month customer retention rate in a subscription-based business model, using the example of Swiggy. You'll learn how to analyze customer behaviour and identify trends, essential skills for data analysts and business intelligence professionals.

Table Creation

CREATE TABLE transactions (
customer_id INT,
transaction_date DATE);

INSERT INTO transactions (customer_id, transaction_date)
VALUES
(1, '2022-01-01' ),
(1, '2022-01-05' ),
(1, '2022-01-08' ),
(1, '2022-01-15' ),
(2, '2022-01-02' ),
(2, '2022-01-09' ),
(2, '2022-01-16' ),
(2, '2022-01-23' ),
(3, '2022-01-03' ),
(3, '2022-01-10' ),
(3, '2022-01-17' ),
(3, '2022-01-24' ),
(4, '2022-01-04' ),
(4, '2022-01-11' ),
(4, '2022-01-18' ),
(4, '2022-01-25' ),
(5, '2022-01-07' ),
(5, '2022-01-14' ),
(5, '2022-01-21' ),
(5, '2022-01-28' ),
(1, '2022-02-01' ),
(1, '2022-02-05' ),
(1, '2022-02-08' ),
(1, '2022-02-15' ),
(2, '2022-02-02' );

select * from transactions;

#interview #analytics #businessanalysis #product #sql #bigquery #mysql #mocktest #union #string #split #substring #pivot #iterate #credit #debit #transaction #selfjoin #innerjoin #chatgpt #cred #subquery #monthly #retentions #sql # retention

#SQLInterviewQuestion, #CustomerRetention, #SubscriptionBusiness #Model, #Swiggy, #dataanalysis , #businessanalystinterview #Intelligence, #DataAnalytics, #MySQL, #SQL Queries

[srinivasareddybandi]

with cte2 as(
select customer_id, extract(month from transaction_date) as month, max(transaction_date)
as mx from transactions
group by 1, 2)

, cte3 as(
select customer_id, month, case when month = 1 then null else month-1 end as previous_month from cte2
order by 2, 1)


select month, count(customer_id) as current_month, count(previous_month) as pm from cte3
group by 1

[sandeepr]

with cte1 as(
Select customer_id,
MONTH(transaction_date) as "Order_month"
from transactions
group by customer_id, MONTH(transaction_date)
)
, cte2 as (
Select *,
by customer_id order by order_month) as "prev_month"
from cte1
)

Select Order_month, count (distinct customer_id ) as "current_month_user"
, count(case when Order_month-prev_month=1 then customer_id end) as "Last_month_user"
from cte2
group by Order_month ;

[mohammadshahbaz]

My solution, plz review:

select mnth, count(distinct customer_id) as curr_mnth_cust
, count(distinct case when delta=1 then customer_id end) as last_mnth_cust
from
(select *, lag(mnth) over(partition by customer_id order by mnth) as lag_mnth
, mnth-lag_mnth as delta
from
(select customer_id, month(TRANSACTION_DATE) as mnth
from transactions
group by 1, 2) a ) b
group by 1;

[vishnukathir]

WITH CTE AS (
SELECT CUSTOMER_ID, MONTHS, LAG(MONTHS, 1, MONTHS) OVER(PARTITION BY CUSTOMER_ID) AS "Lag_Month",
(MONTHS - LAG(MONTHS, 1, MONTHS) OVER(PARTITION BY CUSTOMER_ID)) AS "Month_Diff"
FROM
(SELECT CUSTOMER_ID, EXTRACT(MONTH FROM TRANSACTION_DATE) AS MONTHS FROM TRANSACTIONS ORDER BY 1, 2) A )

SELECT Months, COUNT(DISTINCT CUSTOMER_ID) AS "Current_Month_User",
(CASE WHEN Month_Diff=1 THEN COUNT( DISTINCT CUSTOMER_ID) ELSE 0 END) AS "Last_Month_User"
FROM CTE GROUP BY MONTHS ;


Please correct the above query if approach is wrong

[vikas]

How much time was allotted by the interviewer for this problem ?