210. Comprehensive Patient Journey

WITH patient_admissions AS (
    SELECT p.patient_id, p.first_name || ' ' || p.last_name AS patient_name, ROUND((JULIANDAY('now') - JULIANDAY(p.birth_date)) / 365) AS age, COUNT(a.admission_id) AS total_admissions, GROUP_CONCAT(DISTINCT a.admission_date ORDER BY a.admission_date) AS admission_dates, GROUP_CONCAT(DISTINCT d.last_name) AS doctors_seen, GROUP_CONCAT(DISTINCT a.diagnosis) AS diagnoses, SUM(a.admission_cost) AS cumulative_cost
    FROM patients p
    JOIN admissions a ON p.patient_id = a.patient_id
    JOIN doctors d ON a.attending_doctor_id = d.doctor_id
    GROUP BY p.patient_id, p.first_name, p.last_name, p.birth_date
    HAVING COUNT(a.admission_id) >= 3
)
SELECT patient_name, age, total_admissions, admission_dates, doctors_seen, diagnoses, cumulative_cost
FROM patient_admissions
ORDER BY total_admissions DESC;