03_window_functions.sql

-- ============================================================
-- PART B: SQL WINDOW FUNCTIONS
-- Hospital Management System
-- ============================================================
USE hospital_management;

-- ------------------------------------------------------------
-- B1. RANKING FUNCTIONS
-- Business question: Rank doctors by total revenue generated,
-- both overall and within their own department.
-- Interpretation: ROW_NUMBER gives a unique rank even when
-- revenue ties occur; RANK/DENSE_RANK show how doctors compare
-- when revenue is equal; PERCENT_RANK shows relative standing
-- as a percentage, useful for bonus-tier decisions.
-- ------------------------------------------------------------
SELECT
    CONCAT(d.first_name, ' ', d.last_name) AS doctor_name,
    dep.department_name,
    revenue_summary.total_revenue,
    ROW_NUMBER() OVER (ORDER BY revenue_summary.total_revenue DESC) AS row_num_overall,
    RANK()       OVER (ORDER BY revenue_summary.total_revenue DESC) AS rank_overall,
    DENSE_RANK() OVER (ORDER BY revenue_summary.total_revenue DESC) AS dense_rank_overall,
    ROUND(PERCENT_RANK() OVER (ORDER BY revenue_summary.total_revenue DESC), 2) AS percent_rank_overall,
    RANK()       OVER (PARTITION BY d.department_id ORDER BY revenue_summary.total_revenue DESC) AS rank_within_department
FROM doctors d
JOIN departments dep ON dep.department_id = d.department_id
JOIN (
    SELECT a.doctor_id, SUM(b.amount) AS total_revenue
    FROM appointments a
    JOIN billing b ON b.appointment_id = a.appointment_id
    GROUP BY a.doctor_id
) revenue_summary ON revenue_summary.doctor_id = d.doctor_id
ORDER BY revenue_summary.total_revenue DESC;


-- ------------------------------------------------------------
-- B2. AGGREGATE WINDOW FUNCTIONS
-- Business question: For every bill, show the running total,
-- department average, minimum and maximum bill amount, without
-- collapsing rows via GROUP BY.
-- Interpretation: The running total (SUM OVER) tracks cumulative
-- revenue over time; AVG/MIN/MAX per department let a clerk see
-- immediately whether a given bill is above or below the norm.
-- ------------------------------------------------------------
SELECT
    b.bill_id,
    dept.department_name,
    b.bill_date,
    b.amount,
    SUM(b.amount) OVER (ORDER BY b.bill_date
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
    ROUND(AVG(b.amount) OVER (PARTITION BY dept.department_id), 2) AS department_avg,
    MIN(b.amount) OVER (PARTITION BY dept.department_id) AS department_min,
    MAX(b.amount) OVER (PARTITION BY dept.department_id) AS department_max
FROM billing b
JOIN appointments a ON a.appointment_id = b.appointment_id
JOIN departments dept ON dept.department_id = a.department_id
ORDER BY b.bill_date;


-- ------------------------------------------------------------
-- B3. NAVIGATION FUNCTIONS
-- Business question: For each patient, compare each bill to
-- their previous and next bill amount.
-- Interpretation: LAG shows how much the patient's previous
-- charge was (useful for spotting sudden cost increases); LEAD
-- previews the next charge, useful for finance forecasting.
-- ------------------------------------------------------------
SELECT
    CONCAT(p.first_name, ' ', p.last_name) AS patient_name,
    a.appointment_date,
    b.amount,
    LAG(b.amount, 1) OVER (PARTITION BY p.patient_id ORDER BY a.appointment_date)  AS previous_bill_amount,
    LEAD(b.amount, 1) OVER (PARTITION BY p.patient_id ORDER BY a.appointment_date) AS next_bill_amount,
    b.amount - LAG(b.amount, 1) OVER (PARTITION BY p.patient_id ORDER BY a.appointment_date) AS change_from_previous
FROM billing b
JOIN appointments a ON a.appointment_id = b.appointment_id
JOIN patients p ON p.patient_id = a.patient_id
ORDER BY p.patient_id, a.appointment_date;


-- ------------------------------------------------------------
-- B4. DISTRIBUTION FUNCTIONS
-- Business question: Split doctors into performance quartiles
-- based on revenue generated, and show each doctor's cumulative
-- distribution rank.
-- Interpretation: NTILE(4) buckets doctors into four equally
-- sized performance groups (useful for bonus tiers); CUME_DIST
-- shows the proportion of doctors performing at or below that
-- doctor's revenue level.
-- ------------------------------------------------------------
SELECT
    CONCAT(d.first_name, ' ', d.last_name) AS doctor_name,
    revenue_summary.total_revenue,
    NTILE(4) OVER (ORDER BY revenue_summary.total_revenue DESC) AS performance_quartile,
    ROUND(CUME_DIST() OVER (ORDER BY revenue_summary.total_revenue), 2) AS cumulative_distribution
FROM doctors d
JOIN (
    SELECT a.doctor_id, SUM(b.amount) AS total_revenue
    FROM appointments a
    JOIN billing b ON b.appointment_id = a.appointment_id
    GROUP BY a.doctor_id
) revenue_summary ON revenue_summary.doctor_id = d.doctor_id
ORDER BY revenue_summary.total_revenue DESC;