06_usage_billing_support.sql

-- =====================================================================
-- PROJECT   : Telecom Customer Churn & Revenue Analysis
-- FILE      : 06_usage_billing_support.sql
-- PURPOSE   : Usage patterns, payment behaviour, and support ticket
--             analysis - the operational side of the business.
-- AUTHOR    : Divya Jagtap
-- =====================================================================

USE telecom_churn_db;

-- ---------------------------------------------------------------------
-- Q1. Average data usage vs. plan limit, by plan - are customers
--     over/under-utilising what they pay for?
-- Concepts: JOIN, GROUP BY, aggregate functions, ROUND
-- ---------------------------------------------------------------------
SELECT
    sp.plan_name,
    sp.data_limit_gb,
    ROUND(AVG(u.data_used_gb), 2) AS avg_data_used_gb,
    ROUND(AVG(u.data_used_gb) * 100.0 / sp.data_limit_gb, 1) AS avg_utilisation_pct
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
GROUP BY sp.plan_name, sp.data_limit_gb
ORDER BY avg_utilisation_pct DESC;


-- ---------------------------------------------------------------------
-- Q2. Payment behaviour breakdown - what share of all invoices were
--     Paid / Late / Failed / Pending?
-- Concepts: GROUP BY, CASE WHEN, aggregate functions
-- ---------------------------------------------------------------------
SELECT
    payment_status,
    COUNT(*) AS invoice_count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM billing), 2) AS pct_of_all_invoices,
    ROUND(SUM(billing_amount), 2) AS total_amount
FROM billing
GROUP BY payment_status
ORDER BY invoice_count DESC;


-- ---------------------------------------------------------------------
-- Q3. Customers with repeated payment failures (2 or more Failed
--     invoices) - a strong red flag worth investigating.
-- Concepts: JOIN, GROUP BY, HAVING, subquery
-- ---------------------------------------------------------------------
SELECT
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    c.customer_status,
    COUNT(*) AS failed_payment_count
FROM billing b
JOIN customers c ON c.customer_id = b.customer_id
WHERE b.payment_status = 'Failed'
GROUP BY c.customer_id, customer_name, c.customer_status
HAVING COUNT(*) >= 2
ORDER BY failed_payment_count DESC;


-- ---------------------------------------------------------------------
-- Q4. Support ticket volume and resolution rate by issue type.
-- Concepts: GROUP BY, CASE WHEN, aggregate functions
-- ---------------------------------------------------------------------
SELECT
    issue_type,
    COUNT(*) AS total_tickets,
    SUM(CASE WHEN resolution_status = 'Resolved'  THEN 1 ELSE 0 END) AS resolved,
    SUM(CASE WHEN resolution_status = 'Unresolved' THEN 1 ELSE 0 END) AS unresolved,
    SUM(CASE WHEN resolution_status = 'Escalated' THEN 1 ELSE 0 END) AS escalated,
    ROUND(SUM(CASE WHEN resolution_status = 'Resolved' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS resolution_rate_pct,
    ROUND(AVG(resolution_days), 1) AS avg_resolution_days
FROM support_tickets
GROUP BY issue_type
ORDER BY total_tickets DESC;