02_data_quality.sql

-- =====================================================================
-- PROJECT   : Telecom Customer Churn & Revenue Analysis
-- FILE      : 02_data_quality.sql
-- PURPOSE   : (A) Generate a realistic synthetic dataset entirely in
--                 pure SQL (no external scripts / no CSV imports)
--             (B) Run data-quality checks to prove the generated data
--                 is clean and internally consistent
-- AUTHOR    : Divya Jagtap
-- NOTES     : Run 01_database_schema.sql FIRST. This file assumes an
--             empty database. Re-running it will duplicate data unless
--             you TRUNCATE the tables first (see bottom of this file).
-- =====================================================================

USE telecom_churn_db;

-- MySQL limits recursive CTE depth to 1000 by default. We need ~1500
-- rows for customers, so raise the session limit before generating data.
SET SESSION cte_max_recursion_depth = 100000;


-- =====================================================================
-- SECTION A: DATA GENERATION
-- =====================================================================

-- ---------------------------------------------------------------------
-- A.1  subscription_plans  (6 realistic plans)
-- ---------------------------------------------------------------------
INSERT INTO subscription_plans (plan_name, plan_type, monthly_price, data_limit_gb, call_minutes, sms_limit, is_active) VALUES
('Prepaid Lite',      'Prepaid',  199.00,  45.00,  300,  100, 1),
('Prepaid Standard',  'Prepaid',  349.00,  75.00, 1000,  300, 1),
('Prepaid Premium',   'Prepaid',  599.00, 150.00, 1500,  500, 1),
('Postpaid Standard', 'Postpaid', 499.00, 100.00, 1200,  300, 1),
('Postpaid Premium',  'Postpaid', 899.00, 200.00, 3000, 1000, 1),
('Postpaid Family',   'Postpaid',1499.00, 400.00, 5000, 2000, 1);


-- ---------------------------------------------------------------------
-- A.2  Lookup helper tables (temporary, used only to build fake names)
-- ---------------------------------------------------------------------
CREATE TEMPORARY TABLE tmp_first_names (id INT PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(50));
INSERT INTO tmp_first_names (first_name) VALUES
('Aarav'),('Vivaan'),('Aditya'),('Vihaan'),('Arjun'),('Sai'),('Reyansh'),('Ayaan'),('Krishna'),('Ishaan'),
('Ananya'),('Diya'),('Saanvi'),('Aadhya'),('Kiara'),('Myra'),('Anika'),('Navya'),('Riya'),('Ira'),
('Rohan'),('Karan'),('Devansh'),('Yash'),('Rahul'),('Amit'),('Suresh'),('Priya'),('Sneha'),('Pooja'),
('Neha'),('Divya'),('Kavya'),('Meera'),('Nikhil'),('Varun'),('Siddharth'),('Tanvi'),('Ritu'),('Sanjay');

CREATE TEMPORARY TABLE tmp_last_names (id INT PRIMARY KEY AUTO_INCREMENT, last_name VARCHAR(50));
INSERT INTO tmp_last_names (last_name) VALUES
('Sharma'),('Verma'),('Patil'),('Deshmukh'),('Kulkarni'),('Joshi'),('Iyer'),('Nair'),('Reddy'),('Rao'),
('Gupta'),('Mehta'),('Shah'),('Chavan'),('Pawar'),('Kumar'),('Singh'),('Bhosale'),('Jadhav'),('Naik');

CREATE TEMPORARY TABLE tmp_locations (id INT PRIMARY KEY AUTO_INCREMENT, city VARCHAR(50), state VARCHAR(50));
INSERT INTO tmp_locations (city, state) VALUES
('Pune','Maharashtra'),('Mumbai','Maharashtra'),('Nagpur','Maharashtra'),('Nashik','Maharashtra'),
('Bangalore','Karnataka'),('Mysore','Karnataka'),
('Delhi','Delhi'),
('Chennai','Tamil Nadu'),('Coimbatore','Tamil Nadu'),
('Hyderabad','Telangana'),
('Kolkata','West Bengal'),
('Ahmedabad','Gujarat'),('Surat','Gujarat'),
('Jaipur','Rajasthan'),
('Lucknow','Uttar Pradesh'),
('Chandigarh','Chandigarh'),
('Indore','Madhya Pradesh'),('Bhopal','Madhya Pradesh');

CREATE TEMPORARY TABLE tmp_months (month_date DATE PRIMARY KEY);
INSERT INTO tmp_months (month_date) VALUES
('2025-01-01'),('2025-02-01'),('2025-03-01'),('2025-04-01'),('2025-05-01'),('2025-06-01'),
('2025-07-01'),('2025-08-01'),('2025-09-01'),('2025-10-01'),('2025-11-01'),('2025-12-01');


-- ---------------------------------------------------------------------
-- A.3  customers  (1,500 rows)
-- ---------------------------------------------------------------------
-- signup_date is spread across 2022-01-01 .. ~2024-12-01 so tenure
-- varies naturally (new customers vs. long-time customers).
-- ~22% of customers are marked Churned with a churn_date inside 2025,
-- which is the 12-month analysis window used for usage/billing data.
-- ---------------------------------------------------------------------
INSERT INTO customers (first_name, last_name, gender, age, city, state, signup_date, plan_id, customer_status, churn_date)
WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 1500
)
SELECT
    first_name,
    last_name,
    gender,
    age,
    city,
    state,
    signup_date,
    plan_id,
    customer_status,
    CASE WHEN customer_status = 'Churned'
         THEN DATE_ADD('2025-01-01', INTERVAL FLOOR(RAND() * 334) DAY)
         ELSE NULL
    END AS churn_date
FROM (
    SELECT
        fn.first_name,
        ln.last_name,
        CASE WHEN RAND() < 0.48 THEN 'Male'
             WHEN RAND() < 0.97 THEN 'Female'
             ELSE 'Other' END AS gender,
        FLOOR(18 + RAND() * 47) AS age,
        loc.city,
        loc.state,
        DATE_ADD('2022-01-01', INTERVAL FLOOR(RAND() * 1065) DAY) AS signup_date,
        FLOOR(1 + RAND() * 6) AS plan_id,
        CASE WHEN RAND() < 0.22 THEN 'Churned' ELSE 'Active' END AS customer_status
    FROM seq
    JOIN tmp_first_names fn ON fn.id = FLOOR(1 + RAND() * 40)
    JOIN tmp_last_names  ln ON ln.id = FLOOR(1 + RAND() * 20)
    JOIN tmp_locations  loc ON loc.id = FLOOR(1 + RAND() * 18)
) AS base;


-- ---------------------------------------------------------------------
-- A.4  usage_details  (~12 rows per customer for every active month)
-- ---------------------------------------------------------------------
-- Usage is randomised between 30%-90% of the plan's limit. For customers
-- who churn, usage is deliberately made to DECLINE in the 3 months
-- before churn_date (30% -> 55% -> 75% -> 100% of normal) so that a
-- "declining usage" pattern genuinely exists in the data for the
-- churn-risk model in 08_churn_risk.sql to detect.
-- ---------------------------------------------------------------------
INSERT INTO usage_details (customer_id, usage_month, data_used_gb, call_minutes_used, sms_used)
SELECT
    c.customer_id,
    m.month_date,
    ROUND(
        GREATEST(0.5, p.data_limit_gb * (0.30 + RAND() * 0.60)) *
        CASE
            WHEN c.customer_status = 'Churned' AND TIMESTAMPDIFF(MONTH, m.month_date, c.churn_date) = 0 THEN 0.30
            WHEN c.customer_status = 'Churned' AND TIMESTAMPDIFF(MONTH, m.month_date, c.churn_date) = 1 THEN 0.55
            WHEN c.customer_status = 'Churned' AND TIMESTAMPDIFF(MONTH, m.month_date, c.churn_date) = 2 THEN 0.75
            ELSE 1.00
        END
    , 2) AS data_used_gb,
    FLOOR(
        GREATEST(10, p.call_minutes * (0.30 + RAND() * 0.60)) *
        CASE
            WHEN c.customer_status = 'Churned' AND TIMESTAMPDIFF(MONTH, m.month_date, c.churn_date) = 0 THEN 0.30
            WHEN c.customer_status = 'Churned' AND TIMESTAMPDIFF(MONTH, m.month_date, c.churn_date) = 1 THEN 0.55
            WHEN c.customer_status = 'Churned' AND TIMESTAMPDIFF(MONTH, m.month_date, c.churn_date) = 2 THEN 0.75
            ELSE 1.00
        END
    ) AS call_minutes_used,
    FLOOR(GREATEST(5, p.sms_limit * (0.20 + RAND() * 0.70))) AS sms_used
FROM customers c
JOIN subscription_plans p ON p.plan_id = c.plan_id
JOIN tmp_months m
    ON m.month_date >= DATE_FORMAT(GREATEST(c.signup_date, '2025-01-01'), '%Y-%m-01')
   AND m.month_date <= DATE_FORMAT(IF(c.customer_status = 'Churned', c.churn_date, '2025-12-01'), '%Y-%m-01');


-- ---------------------------------------------------------------------
-- A.5  billing  (~12 rows per customer for every active month)
-- ---------------------------------------------------------------------
-- billing_amount varies +/-... around the plan price (small overage).
-- payment_status is weighted so that customers close to churning are
-- deliberately more likely to have Late/Failed payments -- this is
-- what makes "payment behaviour" a meaningful churn-risk signal later.
-- ---------------------------------------------------------------------
INSERT INTO billing (customer_id, billing_month, billing_amount, due_date, payment_date, payment_status)
SELECT
    customer_id,
    billing_month,
    billing_amount,
    due_date,
    CASE payment_status
        WHEN 'Failed'  THEN NULL
        WHEN 'Pending' THEN NULL
        WHEN 'Paid'    THEN DATE_ADD(due_date, INTERVAL (FLOOR(RAND() * 8) - 3) DAY)
        WHEN 'Late'    THEN DATE_ADD(due_date, INTERVAL (FLOOR(RAND() * 15) + 5) DAY)
    END AS payment_date,
    payment_status
FROM (
    SELECT
        c.customer_id,
        m.month_date AS billing_month,
        ROUND(p.monthly_price * (0.95 + RAND() * 0.20), 2) AS billing_amount,
        DATE_ADD(m.month_date, INTERVAL 15 DAY) AS due_date,
        CASE
            WHEN c.customer_status = 'Churned'
                 AND TIMESTAMPDIFF(MONTH, m.month_date, c.churn_date) BETWEEN 0 AND 1
                 AND RAND() < 0.45 THEN 'Failed'
            WHEN c.customer_status = 'Churned'
                 AND TIMESTAMPDIFF(MONTH, m.month_date, c.churn_date) BETWEEN 0 AND 2
                 AND RAND() < 0.35 THEN 'Late'
            WHEN m.month_date = '2025-12-01' AND c.customer_status = 'Active' AND RAND() < 0.10 THEN 'Pending'
            WHEN RAND() < 0.07 THEN 'Failed'
            WHEN RAND() < 0.15 THEN 'Late'
            ELSE 'Paid'
        END AS payment_status
    FROM customers c
    JOIN subscription_plans p ON p.plan_id = c.plan_id
    JOIN tmp_months m
        ON m.month_date >= DATE_FORMAT(GREATEST(c.signup_date, '2025-01-01'), '%Y-%m-01')
       AND m.month_date <= DATE_FORMAT(IF(c.customer_status = 'Churned', c.churn_date, '2025-12-01'), '%Y-%m-01')
) AS base;


-- ---------------------------------------------------------------------
-- A.6  support_tickets  (~800 rows, unevenly spread across customers)
-- ---------------------------------------------------------------------
-- Churned customers are given a higher chance of "Service Cancellation"
-- / "Payment Failure" tickets close to their churn, again to build a
-- realistic, explainable link between support activity and churn.
-- ---------------------------------------------------------------------
INSERT INTO support_tickets (customer_id, ticket_date, issue_type, resolution_status, resolution_days)
WITH RECURSIVE seq_t AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq_t WHERE n < 800
)
SELECT
    customer_id,
    ticket_date,
    issue_type,
    resolution_status,
    CASE WHEN resolution_status = 'Unresolved' THEN NULL ELSE FLOOR(1 + RAND() * 10) END AS resolution_days
FROM (
    SELECT
        customer_id,
        ticket_date,
        CASE
            WHEN customer_status = 'Churned' AND RAND() < 0.35 THEN
                ELT(FLOOR(1 + RAND() * 2), 'Service Cancellation', 'Payment Failure')
            ELSE
                ELT(FLOOR(1 + RAND() * 7), 'Billing Issue','Network Issue','Data Speed','Plan Change','Service Cancellation','Payment Failure','Other')
        END AS issue_type,
        CASE
            WHEN RAND() < 0.70 THEN 'Resolved'
            WHEN RAND() < 0.90 THEN 'Unresolved'
            ELSE 'Escalated'
        END AS resolution_status
    FROM (
        SELECT
            c.customer_id,
            c.customer_status,
            DATE_ADD(
                GREATEST(c.signup_date, '2025-01-01'),
                INTERVAL FLOOR(RAND() * GREATEST(1, DATEDIFF(
                    IF(c.customer_status = 'Churned', c.churn_date, '2025-12-31'),
                    GREATEST(c.signup_date, '2025-01-01')
                ))) DAY
            ) AS ticket_date
        FROM seq_t
        JOIN customers c
            ON c.customer_id = FLOOR(1 + RAND() * (SELECT COUNT(*) FROM customers))
    ) AS level_a
) AS level_b;


-- Clean up helper tables - not needed once the data is generated
DROP TEMPORARY TABLE IF EXISTS tmp_first_names;
DROP TEMPORARY TABLE IF EXISTS tmp_last_names;
DROP TEMPORARY TABLE IF EXISTS tmp_locations;
DROP TEMPORARY TABLE IF EXISTS tmp_months;


-- =====================================================================
-- SECTION B: DATA QUALITY CHECKS
-- ---------------------------------------------------------------------
-- Every query below SHOULD return 0 rows / 0 counts. If any of them
-- return unexpected results, the dataset has a data-quality issue.
-- This section is a good interview talking point: "how do you verify
-- your data is trustworthy before analysing it?"
-- =====================================================================

-- B.1 Row counts per table (sanity check on volume)
SELECT 'customers'        AS table_name, COUNT(*) AS row_count FROM customers
UNION ALL
SELECT 'subscription_plans', COUNT(*) FROM subscription_plans
UNION ALL
SELECT 'usage_details',      COUNT(*) FROM usage_details
UNION ALL
SELECT 'billing',            COUNT(*) FROM billing
UNION ALL
SELECT 'support_tickets',    COUNT(*) FROM support_tickets;

-- B.2 NULL checks on columns that should never be NULL
SELECT
    SUM(CASE WHEN first_name IS NULL OR last_name IS NULL THEN 1 ELSE 0 END) AS missing_name,
    SUM(CASE WHEN signup_date IS NULL THEN 1 ELSE 0 END)                     AS missing_signup_date,
    SUM(CASE WHEN plan_id IS NULL THEN 1 ELSE 0 END)                        AS missing_plan
FROM customers;

-- B.3 Logical consistency: Active customers must NOT have a churn_date,
--     Churned customers MUST have a churn_date (should return 0 rows)
SELECT customer_id, customer_status, churn_date
FROM customers
WHERE (customer_status = 'Active'  AND churn_date IS NOT NULL)
   OR (customer_status = 'Churned' AND churn_date IS NULL);

-- B.4 Orphan record check: usage/billing/tickets referencing a
--     non-existent customer (should return 0 rows for each)
SELECT 'orphan_usage' AS issue, COUNT(*) AS cnt
FROM usage_details u LEFT JOIN customers c ON u.customer_id = c.customer_id
WHERE c.customer_id IS NULL
UNION ALL
SELECT 'orphan_billing', COUNT(*)
FROM billing b LEFT JOIN customers c ON b.customer_id = c.customer_id
WHERE c.customer_id IS NULL
UNION ALL
SELECT 'orphan_tickets', COUNT(*)
FROM support_tickets t LEFT JOIN customers c ON t.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

-- B.5 Duplicate check: more than one usage/billing row for the same
--     customer in the same month (should return 0 rows - enforced by
--     the UNIQUE constraint in the schema, but good to double-check)
SELECT customer_id, usage_month, COUNT(*) AS dup_count
FROM usage_details
GROUP BY customer_id, usage_month
HAVING COUNT(*) > 1;

-- B.6 Range checks: negative or impossible values
SELECT COUNT(*) AS invalid_usage_rows
FROM usage_details
WHERE data_used_gb < 0 OR call_minutes_used < 0 OR sms_used < 0;

SELECT COUNT(*) AS invalid_billing_rows
FROM billing
WHERE billing_amount <= 0;

-- B.7 Churned customers should have their churn_date AFTER their signup_date
SELECT COUNT(*) AS invalid_churn_dates
FROM customers
WHERE customer_status = 'Churned' AND churn_date <= signup_date;

-- B.8 Every customer should have at least 1 usage row and 1 billing row
--     for the months they were active (spot check - should be 0 rows)
SELECT c.customer_id
FROM customers c
LEFT JOIN usage_details u ON u.customer_id = c.customer_id
WHERE u.usage_id IS NULL;


-- =====================================================================
-- RESET BLOCK (commented out on purpose)
-- ---------------------------------------------------------------------
-- If you need to regenerate the dataset from scratch, run these lines
-- first (in this order, to respect foreign keys), then re-run Section A.
-- =====================================================================
-- TRUNCATE TABLE support_tickets;
-- TRUNCATE TABLE billing;
-- TRUNCATE TABLE usage_details;
-- TRUNCATE TABLE customers;
-- TRUNCATE TABLE subscription_plans;