Customer Churn Analysis Case Study — SQL + Python | Data Analyst Interview
🗄️ SQL + Python Case Study

Customer Churn Analysis — End-to-End SQL + Python Case Study

A complete churn analysis walkthrough — data exploration, cohort analysis, churn segmentation, and a business recommendation. The exact format asked in product company interviews.

Book Free Session ← Case Studies
Case Study Overview
The business problem

A food delivery app has seen monthly active users decline for 3 consecutive months. Your task: identify who is churning, when, and why — then recommend a retention intervention.

📋

Step 1 — Define Churn

Before writing any SQL, align on the definition. Churn = user who was active in month M but made zero orders in month M+1.

  • Active = at least 1 order
  • Churn window = 30 days of inactivity
  • Exclude new users from churn calculation
🔍

Step 2 — Explore the Data

Understand the dataset before analysing it. Check row counts, null rates, date ranges, and user distribution.

  • Total users and active users per month
  • Order frequency distribution
  • Null checks on key columns
📊

Step 3 — Segment Churners

Not all churners are equal. High-value churners need different treatment than low-frequency users who naturally lapse.

  • Segment by order frequency before churn
  • Segment by city / platform
  • Identify the highest-LTV churners first
SQL — Step 1
Identify churned users month by month

Find users who ordered in month M but not in M+1. This is the core churn definition query.

WITH monthly_active AS (
  SELECT
    user_id,
    DATE_TRUNC(‘month’, order_date) AS order_month
  FROM orders
  GROUP BY 1, 2
)
SELECT
  m1.order_month AS active_month,
  COUNT(DISTINCT m1.user_id) AS churned_users
FROM monthly_active m1
LEFT JOIN monthly_active m2
  ON m1.user_id = m2.user_id
  AND m2.order_month = m1.order_month + INTERVAL ‘1 month’
WHERE m2.user_id IS NULL
GROUP BY 1
ORDER BY 1;
Python — Step 2
Segment churners by order frequency

Classify churners into High / Medium / Low value segments based on their order count before churning.

import pandas as pd
 
df = pd.read_csv(‘orders.csv’, parse_dates=[‘order_date’])
 
# Orders per user in the 90 days before they churned
user_freq = df.groupby(‘user_id’)[‘order_id’].count().reset_index()
user_freq.columns = [‘user_id’, ‘order_count’]
 
# Segment into value tiers
user_freq[‘segment’] = pd.cut(
  user_freq[‘order_count’],
  bins=[0, 2, 8, 999],
  labels=[‘Low’, ‘Medium’, ‘High’]
)
 
print(user_freq[‘segment’].value_counts())
Business Recommendation
What to do with the findings

Interviewers want to see you go beyond the analysis to a concrete, prioritised recommendation.

What do you recommend based on this churn analysis?
Priority 1 — High-value churners (8+ orders): These users were your most loyal. Target with a personalised win-back offer within 15 days of churn (before the 30-day habit break). A 20% discount voucher is cheaper than acquiring a new user.

Priority 2 — Early churn (churned after 1-2 orders): These users never formed a habit. Invest in Day-3 and Day-7 onboarding push notifications and an offer on the second order to build the habit.

Guardrail: Monitor margin — discount campaigns can recover revenue gross but destroy unit economics if not capped.
How would you measure if the retention campaign worked?
Run an A/B test on the win-back campaign. Split high-value churners into control (no outreach) and treatment (personalised voucher). Primary metric: 30-day reactivation rate. Guardrail: average order value must not drop below £15 (indicating voucher misuse). Run for 4 weeks minimum. Report back with reactivation rate, revenue recovered, and campaign ROI.

Want to practise case studies like this?

Book a free mock case study session — we’ll walk you through a real dataset and give live feedback on your analysis and recommendation.

Book Free Case Study Mock
Subscribe
Subscribe