SQL Analysis — Customer Churn Analysis

Setup note

Load the cleaned dataset into your database:

CREATE TABLE churn (
    customerID VARCHAR(20), gender VARCHAR(10), SeniorCitizen VARCHAR(3),
    Partner VARCHAR(3), Dependents VARCHAR(3), tenure INT,
    PhoneService VARCHAR(3), InternetService VARCHAR(20),
    Contract VARCHAR(20), PaperlessBilling VARCHAR(3),
    PaymentMethod VARCHAR(40), MonthlyCharges DECIMAL(8,2),
    TotalCharges DECIMAL(10,2), Churn INT,
    tenure_bucket VARCHAR(30), addon_count INT, auto_payment INT
);

---

Q1 — What is the Overall Churn Rate?

SELECT
    COUNT(*)                        AS total_customers,
    SUM(Churn)                      AS churned_customers,
    ROUND(AVG(Churn) * 100, 2)      AS churn_rate_pct,
    ROUND(SUM(CASE WHEN Churn = 0 THEN MonthlyCharges END), 2) AS retained_mrr,
    ROUND(SUM(CASE WHEN Churn = 1 THEN MonthlyCharges END), 2) AS lost_mrr
FROM churn;

Sample output:

total_customers	churned_customers	churn_rate_pct	retained_mrr	lost_mrr
7043	1869	26.54	362,827.40	139,130.55

---

Q2 — Churn Rate by Contract Type

SELECT
    Contract,
    COUNT(*)                        AS customers,
    SUM(Churn)                      AS churned,
    ROUND(AVG(Churn) * 100, 1)      AS churn_rate_pct,
    ROUND(AVG(MonthlyCharges), 2)   AS avg_monthly_charge
FROM churn
GROUP BY Contract
ORDER BY churn_rate_pct DESC;

Sample output:

Contract	customers	churned	churn_rate_pct	avg_monthly_charge
Month-to-month	3875	1655	42.7	66.40
One year	1473	166	11.3	65.05
Two year	1695	48	2.8	60.77

Key finding

Month-to-month customers churn at 15× the rate of two-year contract customers (42.7% vs 2.8%). Converting even 10% of month-to-month customers to a one-year contract could cut total churn by 4 percentage points.

---

Q3 — Churn Rate by Tenure Bucket

SELECT
    tenure_bucket,
    COUNT(*)                        AS customers,
    ROUND(AVG(Churn) * 100, 1)      AS churn_rate_pct,
    ROUND(AVG(MonthlyCharges), 2)   AS avg_charge,
    ROUND(AVG(addon_count), 1)      AS avg_addons
FROM churn
GROUP BY tenure_bucket
ORDER BY
    CASE tenure_bucket
        WHEN 'New (< 6mo)'        THEN 1
        WHEN 'Growing (6-24mo)'   THEN 2
        WHEN 'Established (2-4yr)'THEN 3
        WHEN 'Loyal (4+ yr)'      THEN 4
    END;

Sample output:

tenure_bucket	customers	churn_rate_pct	avg_charge	avg_addons
New (< 6mo)	1099	47.4	67.65	1.8
Growing (6-24mo)	1820	33.6	65.20	2.1
Established (2-4yr)	1756	21.5	64.80	2.4
Loyal (4+ yr)	2368	5.2	59.30	2.8

---

Q4 — Internet Service and Churn

SELECT
    InternetService,
    COUNT(*)                        AS customers,
    ROUND(AVG(Churn) * 100, 1)      AS churn_rate_pct,
    ROUND(AVG(MonthlyCharges), 2)   AS avg_monthly_charge
FROM churn
GROUP BY InternetService
ORDER BY churn_rate_pct DESC;

Sample output:

InternetService	customers	churn_rate_pct	avg_monthly_charge
Fiber optic	3096	41.9	91.37
DSL	2421	19.0	57.18
No	1526	7.4	21.00

---

Q5 — Impact of Add-Ons on Churn

SELECT
    addon_count,
    COUNT(*)                        AS customers,
    ROUND(AVG(Churn) * 100, 1)      AS churn_rate_pct,
    ROUND(AVG(MonthlyCharges), 2)   AS avg_charge
FROM churn
GROUP BY addon_count
ORDER BY addon_count;

---

Q6 — Payment Method and Churn

SELECT
    PaymentMethod,
    COUNT(*)                        AS customers,
    ROUND(AVG(Churn) * 100, 1)      AS churn_rate_pct,
    CASE
        WHEN PaymentMethod IN ('Bank transfer (automatic)', 'Credit card (automatic)')
        THEN 'Automatic' ELSE 'Manual'
    END                             AS payment_type
FROM churn
GROUP BY PaymentMethod
ORDER BY churn_rate_pct DESC;

Sample output:

PaymentMethod	customers	churn_rate_pct	payment_type
Electronic check	2365	45.3	Manual
Mailed check	1612	19.1	Manual
Bank transfer (automatic)	1544	16.7	Automatic
Credit card (automatic)	1522	15.2	Automatic

---

Q7 — At-Risk Customers: Current Month-to-Month with High Churn Signals

-- Find current month-to-month customers who match the profile of churned customers
SELECT
    customerID,
    tenure,
    tenure_bucket,
    Contract,
    InternetService,
    MonthlyCharges,
    addon_count,
    PaymentMethod,
    -- Simple churn score (higher = more at risk)
    (
        CASE Contract WHEN 'Month-to-month' THEN 30 ELSE 0 END +
        CASE WHEN tenure < 12 THEN 20 ELSE 0 END +
        CASE InternetService WHEN 'Fiber optic' THEN 15 ELSE 0 END +
        CASE WHEN MonthlyCharges > 70 THEN 10 ELSE 0 END +
        CASE WHEN addon_count = 0 THEN 15 ELSE 0 END +
        CASE WHEN auto_payment = 0 THEN 10 ELSE 0 END
    )                               AS churn_risk_score
FROM churn
WHERE Churn = 0    -- current customers only (not already churned)
  AND Contract = 'Month-to-month'
ORDER BY churn_risk_score DESC
LIMIT 100;

---

Q8 — Senior Citizens

SELECT
    SeniorCitizen,
    COUNT(*)                        AS customers,
    ROUND(AVG(Churn) * 100, 1)      AS churn_rate_pct,
    ROUND(AVG(MonthlyCharges), 2)   AS avg_charge
FROM churn
GROUP BY SeniorCitizen;

---

Q9 — Cohort Analysis: Churn Rate by Signup Month

-- Requires a signup_date column — add to dataset if available
-- This simulates the cohort query structure
WITH cohorts AS (
    SELECT
        customerID,
        FLOOR(tenure / 6) * 6       AS months_at_signup_bucket,
        Churn
    FROM churn
)
SELECT
    months_at_signup_bucket,
    COUNT(*)                        AS cohort_size,
    ROUND(AVG(Churn) * 100, 1)      AS churn_rate_pct
FROM cohorts
GROUP BY months_at_signup_bucket
ORDER BY months_at_signup_bucket;

---

← Data Cleaning · Next: Dashboard Design →