09_business_questions.sql

-- =====================================================================
-- PROJECT   : Telecom Customer Churn & Revenue Analysis
-- FILE      : 09_business_questions.sql
-- PURPOSE   : 18 realistic business questions, each answered with a
--             short, self-contained SQL query. This file is the one to
--             walk an interviewer through end-to-end.
-- AUTHOR    : Divya Jagtap
-- =====================================================================

USE telecom_churn_db;

-- =====================================================================
-- Q1. What is the overall churn rate?
-- =====================================================================
SELECT
    ROUND(SUM(CASE WHEN customer_status = 'Churned' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS churn_rate_pct
FROM customers;


-- =====================================================================
-- Q2. Which subscription plan has the highest churn rate?
-- =====================================================================
SELECT sp.plan_name,
       ROUND(SUM(CASE WHEN c.customer_status='Churned' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS churn_rate_pct
FROM customers c
JOIN subscription_plans sp ON sp.plan_id = c.plan_id
GROUP BY sp.plan_name
ORDER BY churn_rate_pct DESC
LIMIT 1;


-- =====================================================================
-- Q3. Which city has the most churned customers?
-- =====================================================================
SELECT city, COUNT(*) AS churned_customers
FROM customers
WHERE customer_status = 'Churned'
GROUP BY city
ORDER BY churned_customers DESC
LIMIT 5;


-- =====================================================================
-- Q4. What is the current Monthly Recurring Revenue (most recent
--     billing month with Paid invoices)?
-- =====================================================================
SELECT DATE_FORMAT(billing_month, '%Y-%m') AS month,
       ROUND(SUM(billing_amount), 2) AS mrr
FROM billing
WHERE payment_status = 'Paid'
  AND billing_month = (SELECT MAX(billing_month) FROM billing)
GROUP BY billing_month;


-- =====================================================================
-- Q5. What is the ARPU (Average Revenue Per User) company-wide?
-- =====================================================================
SELECT ROUND(SUM(billing_amount) / COUNT(DISTINCT customer_id), 2) AS arpu
FROM billing
WHERE payment_status = 'Paid';


-- =====================================================================
-- Q6. How much revenue was lost because of churned customers (their
--     billed-but-unpaid or simply-stopped revenue after churn)?
--     Approximation: sum of billing_amount on invoices that were NOT
--     paid, for customers who eventually churned.
-- =====================================================================
SELECT ROUND(SUM(b.billing_amount), 2) AS estimated_lost_revenue
FROM billing b
JOIN customers c ON c.customer_id = b.customer_id
WHERE c.customer_status = 'Churned'
  AND b.payment_status IN ('Failed', 'Pending');


-- =====================================================================
-- Q7. Who are the top 20 highest-value ACTIVE customers (by total
--     revenue paid) - the customers the business most wants to keep?
-- =====================================================================
SELECT c.customer_id, CONCAT(c.first_name,' ',c.last_name) AS customer_name,
       sp.plan_name, ROUND(SUM(b.billing_amount),2) AS total_paid
FROM billing b
JOIN customers c ON c.customer_id = b.customer_id
JOIN subscription_plans sp ON sp.plan_id = c.plan_id
WHERE b.payment_status = 'Paid' AND c.customer_status = 'Active'
GROUP BY c.customer_id, customer_name, sp.plan_name
ORDER BY total_paid DESC
LIMIT 20;


-- =====================================================================
-- Q8. What percentage of customers are on Prepaid vs Postpaid plans,
--     and how does churn differ between the two?
-- =====================================================================
SELECT sp.plan_type,
       COUNT(*) AS total_customers,
       ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM customers), 2) AS pct_of_base,
       ROUND(SUM(CASE WHEN c.customer_status='Churned' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS churn_rate_pct
FROM customers c
JOIN subscription_plans sp ON sp.plan_id = c.plan_id
GROUP BY sp.plan_type;


-- =====================================================================
-- Q9. Which customers have never raised a single support ticket
--     (potentially low-touch, low-risk, easy-to-retain customers)?
-- =====================================================================
SELECT c.customer_id, CONCAT(c.first_name,' ',c.last_name) AS customer_name
FROM customers c
LEFT JOIN support_tickets t ON t.customer_id = c.customer_id
WHERE t.ticket_id IS NULL AND c.customer_status = 'Active'
LIMIT 20;


-- =====================================================================
-- Q10. What is the average tenure (in months) of churned customers
--      vs. active customers? (Do people churn early or late?)
-- =====================================================================
SELECT
    customer_status,
    ROUND(AVG(
        CASE WHEN customer_status = 'Churned'
             THEN TIMESTAMPDIFF(MONTH, signup_date, churn_date)
             ELSE TIMESTAMPDIFF(MONTH, signup_date, CURDATE())
        END
    ), 1) AS avg_tenure_months
FROM customers
GROUP BY customer_status;


-- =====================================================================
-- Q11. Which plan generates the most total revenue overall?
-- =====================================================================
SELECT sp.plan_name, ROUND(SUM(b.billing_amount), 2) AS total_revenue
FROM billing b
JOIN customers c ON c.customer_id = b.customer_id
JOIN subscription_plans sp ON sp.plan_id = c.plan_id
WHERE b.payment_status = 'Paid'
GROUP BY sp.plan_name
ORDER BY total_revenue DESC;


-- =====================================================================
-- Q12. What share of invoices are paid late or fail outright, and is
--      that share getting better or worse month over month?
-- =====================================================================
SELECT
    DATE_FORMAT(billing_month, '%Y-%m') AS month,
    ROUND(SUM(CASE WHEN payment_status IN ('Late','Failed') THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS bad_payment_rate_pct
FROM billing
GROUP BY DATE_FORMAT(billing_month, '%Y-%m')
ORDER BY month;


-- =====================================================================
-- Q13. Which age group churns the most?
-- =====================================================================
SELECT
    CASE
        WHEN age < 25 THEN '18-24'
        WHEN age < 35 THEN '25-34'
        WHEN age < 45 THEN '35-44'
        WHEN age < 55 THEN '45-54'
        ELSE '55+'
    END AS age_group,
    COUNT(*) AS total_customers,
    ROUND(SUM(CASE WHEN customer_status='Churned' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS churn_rate_pct
FROM customers
GROUP BY age_group
ORDER BY churn_rate_pct DESC;


-- =====================================================================
-- Q14. Do customers who raised 2+ support tickets churn more often
--      than customers with 0-1 tickets?
-- =====================================================================
WITH ticket_counts AS (
    SELECT c.customer_id, c.customer_status, COUNT(t.ticket_id) AS ticket_count
    FROM customers c
    LEFT JOIN support_tickets t ON t.customer_id = c.customer_id
    GROUP BY c.customer_id, c.customer_status
)
SELECT
    CASE WHEN ticket_count >= 2 THEN '2+ tickets' ELSE '0-1 tickets' END AS ticket_bucket,
    COUNT(*) AS total_customers,
    ROUND(SUM(CASE WHEN customer_status='Churned' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS churn_rate_pct
FROM ticket_counts
GROUP BY ticket_bucket;


-- =====================================================================
-- Q15. What is the average bill amount for customers who eventually
--      churned vs. those who stayed active? (Is churn price-driven?)
-- =====================================================================
SELECT
    c.customer_status,
    ROUND(AVG(b.billing_amount), 2) AS avg_bill_amount
FROM billing b
JOIN customers c ON c.customer_id = b.customer_id
GROUP BY c.customer_status;


-- =====================================================================
-- Q16. Rank all plans by their retention rate (best to worst).
-- =====================================================================
SELECT
    sp.plan_name,
    ROUND(SUM(CASE WHEN c.customer_status='Active' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS retention_rate_pct,
    RANK() OVER (ORDER BY SUM(CASE WHEN c.customer_status='Active' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) DESC) AS retention_rank
FROM customers c
JOIN subscription_plans sp ON sp.plan_id = c.plan_id
GROUP BY sp.plan_name;


-- =====================================================================
-- Q17. For each customer, how many months in a row (most recent
--      streak) did they use less than 50% of their data plan? Useful
--      to spot slowly disengaging customers.
-- =====================================================================
WITH usage_flag AS (
    SELECT
        u.customer_id,
        u.usage_month,
        CASE WHEN u.data_used_gb < sp.data_limit_gb * 0.5 THEN 1 ELSE 0 END AS is_low_usage
    FROM usage_details u
    JOIN customers c ON c.customer_id = u.customer_id
    JOIN subscription_plans sp ON sp.plan_id = c.plan_id
)
SELECT customer_id,
       SUM(is_low_usage) AS low_usage_months,
       COUNT(*) AS months_tracked
FROM usage_flag
GROUP BY customer_id
HAVING SUM(is_low_usage) >= 3
ORDER BY low_usage_months DESC
LIMIT 20;


-- =====================================================================
-- Q18. Which support issue type is most strongly associated with
--      customers who later churned?
-- =====================================================================
SELECT
    t.issue_type,
    COUNT(*) AS total_tickets,
    SUM(CASE WHEN c.customer_status = 'Churned' THEN 1 ELSE 0 END) AS raised_by_churned_customers,
    ROUND(SUM(CASE WHEN c.customer_status = 'Churned' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS pct_from_churned_customers
FROM support_tickets t
JOIN customers c ON c.customer_id = t.customer_id
GROUP BY t.issue_type
ORDER BY pct_from_churned_customers DESC;