02_cte_queries.sql

-- ============================================================
-- PART A: COMMON TABLE EXPRESSIONS (CTEs)
-- Hospital Management System
-- ============================================================
USE hospital_management;

-- ------------------------------------------------------------
-- A1. SIMPLE CTE
-- Business question: Which patients have a completed appointment
-- and what did they get billed?
-- Business value: Gives front-desk staff a quick, readable list
-- of paid patient visits without repeating the JOIN logic.
-- ------------------------------------------------------------
WITH completed_visits AS (
    SELECT
        a.appointment_id,
        a.patient_id,
        a.appointment_date,
        a.status
    FROM appointments a
    WHERE a.status = 'Completed'
)
SELECT
    p.first_name,
    p.last_name,
    cv.appointment_date,
    b.amount,
    b.payment_status
FROM completed_visits cv
JOIN patients p ON p.patient_id = cv.patient_id
JOIN billing b ON b.appointment_id = cv.appointment_id
ORDER BY cv.appointment_date;


-- ------------------------------------------------------------
-- A2. MULTIPLE CTEs
-- Business question: Which department earned the most revenue,
-- and how many doctors work in each department?
-- Business value: Helps hospital administration see revenue
-- per department alongside staffing levels, useful for
-- resource-allocation decisions.
-- ------------------------------------------------------------
WITH department_revenue AS (
    SELECT
        a.department_id,
        SUM(b.amount) AS total_revenue
    FROM appointments a
    JOIN billing b ON b.appointment_id = a.appointment_id
    GROUP BY a.department_id
),
department_staffing AS (
    SELECT
        department_id,
        COUNT(*) AS doctor_count
    FROM doctors
    GROUP BY department_id
)
SELECT
    d.department_name,
    ds.doctor_count,
    COALESCE(dr.total_revenue, 0) AS total_revenue
FROM departments d
LEFT JOIN department_revenue dr ON dr.department_id = d.department_id
LEFT JOIN department_staffing ds ON ds.department_id = d.department_id
ORDER BY total_revenue DESC;


-- ------------------------------------------------------------
-- A3. RECURSIVE CTE
-- Business question: What is the full reporting chain / org
-- chart for every doctor, starting from the Chief Medical
-- Officer down to the most junior doctor?
-- Business value: Useful for HR to visualize the hospital's
-- management hierarchy and chain of accountability.
-- ------------------------------------------------------------
WITH RECURSIVE doctor_hierarchy AS (
    -- Anchor member: top-level doctor (no supervisor)
    SELECT
        doctor_id,
        first_name,
        last_name,
        specialization,
        supervisor_id,
        1 AS hierarchy_level
    FROM doctors
    WHERE supervisor_id IS NULL

    UNION ALL

    -- Recursive member: doctors who report to someone already in the set
    SELECT
        d.doctor_id,
        d.first_name,
        d.last_name,
        d.specialization,
        d.supervisor_id,
        dh.hierarchy_level + 1
    FROM doctors d
    JOIN doctor_hierarchy dh ON d.supervisor_id = dh.doctor_id
)
SELECT
    hierarchy_level,
    CONCAT(REPEAT('   ', hierarchy_level - 1), first_name, ' ', last_name) AS org_chart,
    specialization
FROM doctor_hierarchy
ORDER BY hierarchy_level, doctor_id;


-- ------------------------------------------------------------
-- A4. CTE WITH AGGREGATION
-- Business question: What is the average and total billing
-- amount generated by each doctor?
-- Business value: Identifies which doctors generate the most
-- billable activity, useful for performance reviews.
-- ------------------------------------------------------------
WITH doctor_billing_summary AS (
    SELECT
        a.doctor_id,
        COUNT(b.bill_id)      AS total_bills,
        SUM(b.amount)         AS total_billed,
        AVG(b.amount)         AS average_bill
    FROM appointments a
    JOIN billing b ON b.appointment_id = a.appointment_id
    GROUP BY a.doctor_id
)
SELECT
    CONCAT(d.first_name, ' ', d.last_name) AS doctor_name,
    d.specialization,
    dbs.total_bills,
    dbs.total_billed,
    ROUND(dbs.average_bill, 2) AS average_bill
FROM doctor_billing_summary dbs
JOIN doctors d ON d.doctor_id = dbs.doctor_id
ORDER BY dbs.total_billed DESC;


-- ------------------------------------------------------------
-- A5. CTE COMBINED WITH JOIN OPERATIONS
-- Business question: Give a full patient visit history showing
-- patient name, doctor, department, and payment status.
-- Business value: A single readable audit trail that a records
-- clerk could use to answer patient billing inquiries quickly.
-- ------------------------------------------------------------
WITH visit_history AS (
    SELECT
        a.appointment_id,
        a.patient_id,
        a.doctor_id,
        a.department_id,
        a.appointment_date,
        a.status
    FROM appointments a
)
SELECT
    CONCAT(p.first_name, ' ', p.last_name)   AS patient_name,
    CONCAT(doc.first_name, ' ', doc.last_name) AS doctor_name,
    dept.department_name,
    vh.appointment_date,
    vh.status,
    b.amount,
    b.payment_status
FROM visit_history vh
JOIN patients p     ON p.patient_id = vh.patient_id
JOIN doctors doc    ON doc.doctor_id = vh.doctor_id
JOIN departments dept ON dept.department_id = vh.department_id
LEFT JOIN billing b ON b.appointment_id = vh.appointment_id
ORDER BY vh.appointment_date;