206. High-Cost Patient Cohort

WITH patient_costs AS (
    SELECT p.patient_id, p.first_name, p.last_name, p.birth_date, COUNT(a.admission_id) AS admission_count, SUM(a.admission_cost) AS total_cost, AVG(a.admission_cost) AS avg_cost, MAX(a.admission_date) AS last_admission
    FROM patients p
    JOIN admissions a ON p.patient_id = a.patient_id
    GROUP BY p.patient_id, p.first_name, p.last_name, p.birth_date
)
SELECT first_name || ' ' || last_name AS patient_name, birth_date, admission_count, total_cost, avg_cost, last_admission
FROM patient_costs
WHERE total_cost >= (SELECT total_cost FROM patient_costs ORDER BY total_cost DESC LIMIT 1 OFFSET (SELECT COUNT(*) FROM patient_costs) / 10)
ORDER BY total_cost DESC;