Medication Analysis — Prescribing Patterns and Polypharmacy

Medication data reveals prescribing habits, treatment duration, and polypharmacy risk. This lesson covers the most prescribed drugs, duration analysis, concurrent medication counts, and drug-ward correlations.

1. Most Prescribed Drugs

SELECT
    drug_name,
    COUNT(*)                          AS prescriptions,
    ROUND(AVG(dosage_mg), 1)          AS avg_dosage_mg,
    COUNT(DISTINCT admission_id)      AS distinct_admissions,
    (ARRAY_AGG(frequency ORDER BY frequency))[1] AS most_common_frequency
FROM hc_medications
GROUP BY drug_name
ORDER BY prescriptions DESC;

What This Returns

drug_name         | prescriptions | avg_dosage_mg | distinct_admissions
------------------+---------------+---------------+--------------------
Metoprolol        |            80 |          25.0 |                  80
Lisinopril        |            80 |          10.0 |                  80
Atorvastatin      |            80 |          40.0 |                  80
...

2. Average Medication Duration by Drug

Duration = end_date - start_date. Admissions with open-ended medications (end_date IS NULL) get a duration up to today:

SELECT
    drug_name,
    route,
    COUNT(*)                                               AS prescriptions,
    ROUND(AVG(
        COALESCE(end_date, CURRENT_DATE) - start_date
    ), 1)                                                  AS avg_duration_days,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (
        ORDER BY COALESCE(end_date, CURRENT_DATE) - start_date
    )::NUMERIC, 1)                                         AS median_duration_days,
    COUNT(*) FILTER (WHERE end_date IS NULL)               AS open_prescriptions
FROM hc_medications
GROUP BY drug_name, route
ORDER BY avg_duration_days DESC;

3. Polypharmacy — Admissions with 5+ Concurrent Medications

Polypharmacy (5+ drugs simultaneously) is a major adverse event risk. Detect it by finding admissions where multiple medications overlap on the same date:

WITH daily_counts AS (
    SELECT
        m.admission_id,
        d.day,
        COUNT(m.id) AS concurrent_meds
    FROM hc_medications m
    CROSS JOIN LATERAL (
        SELECT generate_series(
            m.start_date,
            COALESCE(m.end_date, m.start_date + 30),
            INTERVAL '1 day'
        )::DATE AS day
    ) d
    GROUP BY m.admission_id, d.day
)
SELECT
    dc.admission_id,
    p.name          AS patient_name,
    p.mrn,
    MAX(dc.concurrent_meds) AS peak_concurrent_meds,
    COUNT(DISTINCT dc.day) FILTER (WHERE dc.concurrent_meds >= 5) AS days_polypharmacy
FROM daily_counts dc
JOIN hc_admissions a ON a.id = dc.admission_id
JOIN hc_patients p   ON p.id = a.patient_id
WHERE dc.concurrent_meds >= 5
GROUP BY dc.admission_id, p.name, p.mrn
ORDER BY peak_concurrent_meds DESC, days_polypharmacy DESC;

4. Drug-Ward Correlation

Which wards prescribe which drugs most often? This informs formulary decisions:

SELECT
    w.name            AS ward,
    m.drug_name,
    COUNT(m.id)       AS prescriptions,
    ROUND(100.0 * COUNT(m.id) / SUM(COUNT(m.id)) OVER (PARTITION BY w.name), 1) AS pct_of_ward_prescriptions
FROM hc_medications m
JOIN hc_admissions a ON a.id = m.admission_id
JOIN hc_wards w      ON w.id = a.ward_id
GROUP BY w.name, m.drug_name
ORDER BY w.name, prescriptions DESC;

5. Antibiotic Stewardship — Duration Outliers

Identify unusually long antibiotic courses (proxy for stewardship gaps):

SELECT
    m.admission_id,
    m.drug_name,
    m.start_date,
    m.end_date,
    m.end_date - m.start_date        AS duration_days,
    p.name,
    p.mrn,
    w.name                           AS ward
FROM hc_medications m
JOIN hc_admissions a ON a.id = m.admission_id
JOIN hc_patients p   ON p.id = a.patient_id
JOIN hc_wards w      ON w.id = a.ward_id
WHERE m.drug_name = 'Amoxicillin'
  AND m.end_date IS NOT NULL
  AND (m.end_date - m.start_date) > 14
ORDER BY duration_days DESC;

Key Takeaway

The CROSS JOIN LATERAL generate_series trick expands each medication row into one row per day, enabling concurrent-medication counting at daily granularity. This is more accurate than just counting medications per admission because it respects the actual overlap window. Use the same pattern for overlapping bookings, overlapping contracts, or any "resource contention at a point in time" problem.

← Previous Next →