= Advanced Reports =


----

== Doctor performance analytics ==

This report identifies which doctors are performing best over a given date range (`report_start`–`report_end`, set in the `params` CTE). Every doctor with at least one appointment, performed procedure, ordered lab test, or referral in the range is included; doctors with no activity in the range are left out of the ranking. For each doctor the report shows:

* Appointment volume, split into completed, cancelled, and open (`SCHEDULED` or `IN_PROGRESS`) appointments, and the completion rate. The completion rate only considers resolved appointments, `COMPLETED` / (`COMPLETED` + `CANCELLED`), so open appointments, including future ones, do not lower it.
* Procedure volume, revenue, and the procedure documentation rate: the share of the doctor's performed procedures that have a result recorded in `procedure_results`.
* Lab test volume, revenue, and the lab result availability rate: the share of ordered test types that have at least one result in `lab_results`.
* Referral volume and the referral follow-up rate is the share of referrals where the patient had a non-cancelled appointment with the receiving doctor after the referral date.
* Breadth of care, measured as the number of distinct patients touched across procedures, lab tests, and appointments combined.

Revenue is calculated at list price, as the number of performed procedures or ordered tests multiplied by the `cost` in `procedures` and `lab_tests`; it does not depend on billing or payment status.

Six factors feed into one weighted performance_score: appointment completion (20%),
procedure documentation (20%), lab result availability (15%), and referral follow-up
(15%) each contribute a percentage-based rate, while procedure volume (0.30 points
per procedure, capped at 50) and patient breadth (0.20 points per patient, capped at
100) contribute capped counts, so one very busy doctor can't dominate the score.
Revenue, lab test volume, and referral volume are reported for context but do not
affect the score. A rate that cannot be calculated (for example, a doctor with no lab
tests, or with only open appointments) counts as 0. Doctors are then ranked with
DENSE_RANK(), so tied scores share a rank instead of skipping numbers.

Because `procedure_results` and `lab_results` reference a procedure or test type rather
than a specific performed procedure or test, a result counts as documentation for every
performance of that procedure or test type.


=== SQL ===

{{{
WITH params AS (
    SELECT
        CAST('2025-01-01' AS DATE) AS report_start,
        CAST('2026-12-31' AS DATE) AS report_end
),
documented_procedure_ids AS (
    SELECT DISTINCT procedure_id FROM procedure_results
),
documented_test_ids AS (
    SELECT DISTINCT test_id FROM lab_results
),
doctor_appointments AS (
    SELECT
        d.doctor_id, d.first_name, d.last_name, d.email_address,
        ds.specialization_name, dept.department_name,
        COUNT(a.appointment_id) AS total_appointments,
        COUNT(a.appointment_id) FILTER (WHERE a.status = 'COMPLETED') AS completed_appointments,
        COUNT(a.appointment_id) FILTER (WHERE a.status = 'CANCELLED') AS cancelled_appointments,
        COUNT(a.appointment_id) FILTER (WHERE a.status IN ('SCHEDULED', 'IN_PROGRESS')) AS open_appointments,
        ROUND(100.0 * COUNT(a.appointment_id) FILTER (WHERE a.status = 'COMPLETED')
            / NULLIF(COUNT(a.appointment_id) FILTER (WHERE a.status IN ('COMPLETED', 'CANCELLED')), 0), 2)
            AS appointment_completion_rate
    FROM doctors d
        JOIN doctor_specialization ds ON d.specialization_id = ds.specialization_id
        JOIN departments dept ON d.department_id = dept.department_id
        CROSS JOIN params p
        LEFT JOIN appointments a
            ON a.doctor_id = d.doctor_id
            AND a.appointment_date >= p.report_start
            AND a.appointment_date <= p.report_end
    GROUP BY d.doctor_id, d.first_name, d.last_name, d.email_address, ds.specialization_name, dept.department_name
),
doctor_procedures AS (
    SELECT
        d.doctor_id,
        COUNT(*) AS procedures_performed,
        ROUND(AVG(proc.cost)::numeric, 2) AS avg_procedure_cost,
        SUM(proc.cost) AS total_procedure_revenue,
        ROUND(100.0 * COUNT(dpi.procedure_id) / NULLIF(COUNT(*), 0), 2) AS procedure_documentation_rate
    FROM doctors d
        JOIN performed_procedures pp ON d.doctor_id = pp.doctor_id
        JOIN procedures proc ON pp.procedure_id = proc.procedure_id
        LEFT JOIN documented_procedure_ids dpi ON dpi.procedure_id = pp.procedure_id
        JOIN params p ON pp.procedure_date >= p.report_start AND pp.procedure_date <= p.report_end
    GROUP BY d.doctor_id
),
doctor_lab_tests AS (
    SELECT
        d.doctor_id,
        COUNT(*) AS lab_tests_ordered,
        ROUND(AVG(lt.cost)::numeric, 2) AS avg_test_cost,
        SUM(lt.cost) AS total_lab_revenue,
        ROUND(100.0 * COUNT(DISTINCT dti.test_id) / NULLIF(COUNT(DISTINCT plt.test_id), 0), 2) AS lab_result_availability_rate
    FROM doctors d
        JOIN performed_lab_tests plt ON d.doctor_id = plt.doctor_id
        JOIN lab_tests lt ON plt.test_id = lt.test_id
        LEFT JOIN documented_test_ids dti ON dti.test_id = plt.test_id
        JOIN params p ON plt.test_date >= p.report_start AND plt.test_date <= p.report_end
    GROUP BY d.doctor_id
),
doctor_referrals AS (
    SELECT
        d.doctor_id,
        COUNT(DISTINCT ref.referral_id) AS referrals_made,
        ROUND(100.0 * COUNT(DISTINCT CASE WHEN EXISTS (
            SELECT 1 FROM appointments fa
            WHERE fa.doctor_id = ref.to_doctor_id
              AND fa.patient_id = mr.patient_id
              AND fa.appointment_date > ref.referral_date
              AND fa.status <> 'CANCELLED'
        ) THEN ref.referral_id END) / NULLIF(COUNT(DISTINCT ref.referral_id), 0), 2) AS referral_followup_rate
    FROM doctors d
        JOIN referrals ref ON d.doctor_id = ref.from_doctor_id
        JOIN medical_records mr ON ref.record_id = mr.record_id
        JOIN params p ON ref.referral_date >= p.report_start AND ref.referral_date <= p.report_end
    GROUP BY d.doctor_id
),
doctor_patient_touchpoints AS (
    SELECT pp.doctor_id, pp.patient_id FROM performed_procedures pp
        JOIN params p ON pp.procedure_date >= p.report_start AND pp.procedure_date <= p.report_end
    UNION
    SELECT plt.doctor_id, plt.patient_id FROM performed_lab_tests plt
        JOIN params p ON plt.test_date >= p.report_start AND plt.test_date <= p.report_end
    UNION
    SELECT a.doctor_id, a.patient_id FROM appointments a
        JOIN params p ON a.appointment_date >= p.report_start AND a.appointment_date <= p.report_end
),
doctor_unique_patients AS (
    SELECT doctor_id, COUNT(DISTINCT patient_id) AS total_unique_patients
    FROM doctor_patient_touchpoints
    GROUP BY doctor_id
),
doctor_scores AS (
    SELECT
        da.doctor_id, da.first_name, da.last_name, da.email_address,
        da.specialization_name, da.department_name,
        da.total_appointments, da.completed_appointments, da.cancelled_appointments, da.open_appointments,
        COALESCE(da.appointment_completion_rate, 0) AS appointment_completion_rate,
        COALESCE(dp.procedures_performed, 0) AS procedures_performed,
        COALESCE(dp.avg_procedure_cost, 0) AS avg_procedure_cost,
        COALESCE(dp.total_procedure_revenue, 0) AS total_procedure_revenue,
        COALESCE(dp.procedure_documentation_rate, 0) AS procedure_documentation_rate,
        COALESCE(dl.lab_tests_ordered, 0) AS lab_tests_ordered,
        COALESCE(dl.avg_test_cost, 0) AS avg_test_cost,
        COALESCE(dl.total_lab_revenue, 0) AS total_lab_revenue,
        COALESCE(dl.lab_result_availability_rate, 0) AS lab_result_availability_rate,
        COALESCE(dr.referrals_made, 0) AS referrals_made,
        COALESCE(dr.referral_followup_rate, 0) AS referral_followup_rate,
        COALESCE(dup.total_unique_patients, 0) AS total_unique_patients,
        ROUND(
            COALESCE(da.appointment_completion_rate, 0) * 0.20
            + COALESCE(dp.procedure_documentation_rate, 0) * 0.20
            + COALESCE(dl.lab_result_availability_rate, 0) * 0.15
            + COALESCE(dr.referral_followup_rate, 0) * 0.15
            + LEAST(COALESCE(dp.procedures_performed, 0), 50) * 0.30
            + LEAST(COALESCE(dup.total_unique_patients, 0), 100) * 0.20
        , 2) AS performance_score
    FROM doctor_appointments da
        LEFT JOIN doctor_procedures dp ON da.doctor_id = dp.doctor_id
        LEFT JOIN doctor_lab_tests dl ON da.doctor_id = dl.doctor_id
        LEFT JOIN doctor_referrals dr ON da.doctor_id = dr.doctor_id
        LEFT JOIN doctor_unique_patients dup ON da.doctor_id = dup.doctor_id
)
SELECT *,
    DENSE_RANK() OVER (ORDER BY performance_score DESC) AS performance_rank
FROM doctor_scores
WHERE total_appointments > 0
   OR procedures_performed > 0
   OR lab_tests_ordered > 0
   OR referrals_made > 0
ORDER BY performance_rank, last_name, first_name;
}}}

=== Relational Algebra ===

{{{
Params ← {(report_start, report_end)}

DocumentedProcedureIds ← π_{procedure_id} (procedure_results)
DocumentedTestIds ← π_{test_id} (lab_results)

DoctorAppointments ←
γ
  doctor_id := d.doctor_id; first_name := d.first_name; last_name := d.last_name;
  email_address := d.email_address; specialization_name := ds.specialization_name;
  department_name := dept.department_name;
  total_appointments := COUNT(a.appointment_id);
  completed_appointments := COUNT(a.appointment_id) FILTER (a.status = 'COMPLETED');
  cancelled_appointments := COUNT(a.appointment_id) FILTER (a.status = 'CANCELLED');
  open_appointments := COUNT(a.appointment_id) FILTER (a.status ∈ {'SCHEDULED', 'IN_PROGRESS'});
  appointment_completion_rate := ROUND(100.0 * COUNT(a.appointment_id) FILTER (a.status = 'COMPLETED')
                                 / COUNT(a.appointment_id) FILTER (a.status ∈ {'COMPLETED', 'CANCELLED'}), 2)
(
  (
    (
      (doctors d ⨝ (d.specialization_id = ds.specialization_id) doctor_specialization ds)
      ⨝ (d.department_id = dept.department_id) departments dept
    )
    × Params p
  )
  ⟕ (d.doctor_id = a.doctor_id ∧ a.appointment_date ≥ p.report_start ∧ a.appointment_date ≤ p.report_end) appointments a
)

DoctorProcedures ←
γ
  doctor_id := d.doctor_id;
  procedures_performed := COUNT(*);
  avg_procedure_cost := ROUND(AVG(proc.cost), 2);
  total_procedure_revenue := SUM(proc.cost);
  procedure_documentation_rate := ROUND(100.0 * COUNT(dpi.procedure_id) / COUNT(*), 2)
(
  σ (pp.procedure_date ≥ p.report_start ∧ pp.procedure_date ≤ p.report_end)
  (
    (
      (
        (doctors d ⨝ (d.doctor_id = pp.doctor_id) performed_procedures pp)
        ⨝ (pp.procedure_id = proc.procedure_id) procedures proc
      )
      ⟕ (pp.procedure_id = dpi.procedure_id) DocumentedProcedureIds dpi
    )
    × Params p
  )
)

DoctorLabTests ←
γ
  doctor_id := d.doctor_id;
  lab_tests_ordered := COUNT(*);
  avg_test_cost := ROUND(AVG(lt.cost), 2);
  total_lab_revenue := SUM(lt.cost);
  lab_result_availability_rate := ROUND(100.0 * COUNT_DISTINCT(dti.test_id) / COUNT_DISTINCT(plt.test_id), 2)
(
  σ (plt.test_date ≥ p.report_start ∧ plt.test_date ≤ p.report_end)
  (
    (
      (
        (doctors d ⨝ (d.doctor_id = plt.doctor_id) performed_lab_tests plt)
        ⨝ (plt.test_id = lt.test_id) lab_tests lt
      )
      ⟕ (plt.test_id = dti.test_id) DocumentedTestIds dti
    )
    × Params p
  )
)

FollowedUpReferrals ←
π_{referral_id}
(
  σ (fa.doctor_id = ref.to_doctor_id ∧ fa.patient_id = mr.patient_id
     ∧ fa.appointment_date > ref.referral_date ∧ fa.status ≠ 'CANCELLED')
  (
    (referrals ref ⨝ (ref.record_id = mr.record_id) medical_records mr)
    × appointments fa
  )
)

DoctorReferrals ←
γ
  doctor_id := d.doctor_id;
  referrals_made := COUNT_DISTINCT(ref.referral_id);
  referral_followup_rate := ROUND(100.0 * COUNT_DISTINCT(fur.referral_id) / COUNT_DISTINCT(ref.referral_id), 2)
(
  σ (ref.referral_date ≥ p.report_start ∧ ref.referral_date ≤ p.report_end)
  (
    (
      (
        (doctors d ⨝ (d.doctor_id = ref.from_doctor_id) referrals ref)
        ⨝ (ref.record_id = mr.record_id) medical_records mr
      )
      ⟕ (ref.referral_id = fur.referral_id) FollowedUpReferrals fur
    )
    × Params p
  )
)

DoctorPatientTouchpoints ←
π_{doctor_id, patient_id}
(
  σ (pp.procedure_date ≥ p.report_start ∧ pp.procedure_date ≤ p.report_end) (performed_procedures pp × Params p)
)
∪
π_{doctor_id, patient_id}
(
  σ (plt.test_date ≥ p.report_start ∧ plt.test_date ≤ p.report_end) (performed_lab_tests plt × Params p)
)
∪
π_{doctor_id, patient_id}
(
  σ (a.appointment_date ≥ p.report_start ∧ a.appointment_date ≤ p.report_end) (appointments a × Params p)
)

DoctorUniquePatients ←
γ doctor_id; total_unique_patients := COUNT_DISTINCT(patient_id)
(DoctorPatientTouchpoints)

DoctorScores ←
π
  doctor_id, first_name, last_name, email_address, specialization_name, department_name,
  total_appointments, completed_appointments, cancelled_appointments, open_appointments,
  appointment_completion_rate := COALESCE(da.appointment_completion_rate, 0),
  procedures_performed := COALESCE(dp.procedures_performed, 0),
  avg_procedure_cost := COALESCE(dp.avg_procedure_cost, 0),
  total_procedure_revenue := COALESCE(dp.total_procedure_revenue, 0),
  procedure_documentation_rate := COALESCE(dp.procedure_documentation_rate, 0),
  lab_tests_ordered := COALESCE(dl.lab_tests_ordered, 0),
  avg_test_cost := COALESCE(dl.avg_test_cost, 0),
  total_lab_revenue := COALESCE(dl.total_lab_revenue, 0),
  lab_result_availability_rate := COALESCE(dl.lab_result_availability_rate, 0),
  referrals_made := COALESCE(dr.referrals_made, 0),
  referral_followup_rate := COALESCE(dr.referral_followup_rate, 0),
  total_unique_patients := COALESCE(dup.total_unique_patients, 0),
  performance_score :=
    ROUND(
      COALESCE(da.appointment_completion_rate, 0) * 0.20
      + COALESCE(dp.procedure_documentation_rate, 0) * 0.20
      + COALESCE(dl.lab_result_availability_rate, 0) * 0.15
      + COALESCE(dr.referral_followup_rate, 0) * 0.15
      + LEAST(COALESCE(dp.procedures_performed, 0), 50) * 0.30
      + LEAST(COALESCE(dup.total_unique_patients, 0), 100) * 0.20
    , 2)
(
  (
    (
      (DoctorAppointments da
      ⟕ (da.doctor_id = dp.doctor_id) DoctorProcedures dp)
      ⟕ (da.doctor_id = dl.doctor_id) DoctorLabTests dl
    )
    ⟕ (da.doctor_id = dr.doctor_id) DoctorReferrals dr
  )
  ⟕ (da.doctor_id = dup.doctor_id) DoctorUniquePatients dup
)

ActiveDoctors ←
σ (total_appointments > 0 ∨ procedures_performed > 0 ∨ lab_tests_ordered > 0 ∨ referrals_made > 0)
(DoctorScores)

RankedDoctors ←
rank_dense
  performance_rank := ORDER BY performance_score DESC
(ActiveDoctors)

Result ←
τ performance_rank ASC, last_name ASC, first_name ASC
(RankedDoctors)
}}}

----

== Patient health risk assessment ==


This report identifies which patients carry the highest clinical risk, for proactive outreach and care-coordination purposes, combining:

* Chronic condition burden is the number of distinct diagnoses on file, capped at 10 in scoring so one outlier patient can't dominate the whole composite score, plus the count of severe symptoms.
* Allergy severity reflects the worst allergy severity level on file for the patient (`NONE` if the patient has no documented allergies).
* A direct medication safety flag counts how many of the patient's prescribed medications are linked, via `allergy_prescription_restrictions`, to one of their documented allergies. It is one of the most heavily weighted signals in the score, worth 10 points per conflicting medication, and is not capped.
* Polypharmacy tier reflects how many distinct medications the patient has on record, bucketed into `LOW_POLYPHARMACY`, `MODERATE_POLYPHARMACY`, or `HIGH_POLYPHARMACY`. Prescription records carry no dates or active status, so every medication ever prescribed to the patient is treated as current.
* Appointment adherence covers the completion rate of past appointments within the lookback window set by the `:lookback_months` parameter (future scheduled appointments are excluded), and the number of days since the patient's last completed appointment across their full history. Patients with no completed appointment, or none in the last 180 days, receive additional points.
* Referral burden is the number of referrals in the last 6 months.
* Age acts as a standard clinical risk modifier, with patients over 65 receiving additional points.

Only the diagnosis count is capped; severe symptoms, medication conflicts, and referrals add points without an upper limit.
These combine into a `risk_score`, bucketed into `CRITICAL`/`HIGH`/`MODERATE`/`LOW`, and ranked with DENSE_RANK().

=== SQL ===

{{{
WITH params AS (
    SELECT
        CAST(:lookback_months AS INTEGER) AS lookback_months,
        CURRENT_DATE - (CAST(:lookback_months AS INTEGER) * INTERVAL '1 month') AS risk_assessment_start
),
patient_records AS (
    SELECT p.patient_id, mr.record_id
    FROM patients p
        LEFT JOIN medical_records mr ON mr.patient_id = p.patient_id
),
patient_chronic_conditions AS (
    SELECT
        p.patient_id, p.first_name, p.last_name, p.embg,
        EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.date_of_birth))::int AS age,
        COUNT(DISTINCT d.diagnosis_id) AS chronic_diagnoses_count,
        COUNT(DISTINCT CASE WHEN UPPER(mrs.severity) = 'SEVERE' THEN mrs.symptom_id END) AS severe_symptoms_count
    FROM patients p
        LEFT JOIN diagnosis d ON d.patient_id = p.patient_id
        LEFT JOIN patient_records pr ON pr.patient_id = p.patient_id
        LEFT JOIN medical_record_symptoms mrs ON mrs.record_id = pr.record_id
    GROUP BY p.patient_id, p.first_name, p.last_name, p.embg, p.date_of_birth
),
patient_medication_profile AS (
    SELECT
        p.patient_id,
        COUNT(DISTINCT pmr.prescription_id) AS current_medications,
        CASE
            WHEN COUNT(DISTINCT pmr.prescription_id) >= 5 THEN 'HIGH_POLYPHARMACY'
            WHEN COUNT(DISTINCT pmr.prescription_id) >= 3 THEN 'MODERATE_POLYPHARMACY'
            ELSE 'LOW_POLYPHARMACY'
        END AS polypharmacy_status,
        COUNT(DISTINCT pa.allergy_id) AS allergy_count,
        CASE
            WHEN COUNT(DISTINCT CASE WHEN a.allergy_severity = 'CRITICAL' THEN pa.allergy_id END) > 0 THEN 'CRITICAL'
            WHEN COUNT(DISTINCT CASE WHEN a.allergy_severity = 'HIGH' THEN pa.allergy_id END) > 0 THEN 'HIGH'
            WHEN COUNT(DISTINCT CASE WHEN a.allergy_severity = 'MEDIUM' THEN pa.allergy_id END) > 0 THEN 'MEDIUM'
            WHEN COUNT(DISTINCT pa.allergy_id) > 0 THEN 'LOW'
            ELSE 'NONE'
        END AS max_allergy_severity,
        COUNT(DISTINCT pmr2.prescription_id) AS conflicting_medications
    FROM patients p
        LEFT JOIN patient_records pr ON pr.patient_id = p.patient_id
        LEFT JOIN prescription_medical_records pmr ON pmr.record_id = pr.record_id
        LEFT JOIN patient_allergies pa ON pa.patient_id = p.patient_id
        LEFT JOIN allergies a ON a.allergy_id = pa.allergy_id
        LEFT JOIN allergy_prescription_restrictions apr ON apr.allergy_id = a.allergy_id
        LEFT JOIN prescription_restriction restr ON restr.restriction_id = apr.restriction_id
        LEFT JOIN prescription_medical_records pmr2
            ON pmr2.record_id = pr.record_id AND pmr2.prescription_id = restr.prescription_id
    GROUP BY p.patient_id
),
patient_activity AS (
    SELECT
        p.patient_id,
        ROUND(100.0 * COUNT(a.appointment_id) FILTER (WHERE a.status = 'COMPLETED')
            / NULLIF(COUNT(a.appointment_id), 0), 2) AS appointment_completion_rate
    FROM patients p
        CROSS JOIN params prm
        LEFT JOIN appointments a
            ON a.patient_id = p.patient_id
            AND a.appointment_date >= prm.risk_assessment_start
            AND a.appointment_date <= CURRENT_DATE
    GROUP BY p.patient_id
),
patient_last_visit AS (
    SELECT
        patient_id,
        CURRENT_DATE - MAX(appointment_date) AS days_since_last_appointment
    FROM appointments
    WHERE status = 'COMPLETED'
      AND appointment_date <= CURRENT_DATE
    GROUP BY patient_id
),
patient_referrals AS (
    SELECT
        p.patient_id,
        COUNT(DISTINCT CASE WHEN r.referral_date > CURRENT_DATE - INTERVAL '6 months' THEN r.referral_id END)
            AS referrals_last_6_months
    FROM patients p
        LEFT JOIN patient_records pr ON pr.patient_id = p.patient_id
        LEFT JOIN referrals r ON r.record_id = pr.record_id
    GROUP BY p.patient_id
),
patient_risk_scores AS (
    SELECT
        pcc.patient_id, pcc.first_name, pcc.last_name, pcc.embg, pcc.age,
        pcc.chronic_diagnoses_count, pcc.severe_symptoms_count,
        COALESCE(pmp.allergy_count, 0) AS allergy_count,
        COALESCE(pmp.max_allergy_severity, 'NONE') AS max_allergy_severity,
        COALESCE(pmp.conflicting_medications, 0) AS conflicting_medications,
        COALESCE(pmp.current_medications, 0) AS current_medications,
        COALESCE(pmp.polypharmacy_status, 'LOW_POLYPHARMACY') AS polypharmacy_status,
        COALESCE(pa.appointment_completion_rate, 100) AS appointment_completion_rate,
        COALESCE(plv.days_since_last_appointment, 9999) AS days_since_last_appointment,
        COALESCE(pr.referrals_last_6_months, 0) AS referrals_last_6_months,
        ROUND(
            LEAST(COALESCE(pcc.chronic_diagnoses_count, 0), 10) * 3
            + COALESCE(pcc.severe_symptoms_count, 0) * 4
            + CASE COALESCE(pmp.max_allergy_severity, 'NONE')
                  WHEN 'CRITICAL' THEN 20 WHEN 'HIGH' THEN 10 WHEN 'MEDIUM' THEN 5 ELSE 0 END
            + CASE COALESCE(pmp.polypharmacy_status, 'LOW_POLYPHARMACY')
                  WHEN 'HIGH_POLYPHARMACY' THEN 15 WHEN 'MODERATE_POLYPHARMACY' THEN 8 ELSE 0 END
            + COALESCE(pmp.conflicting_medications, 0) * 10
            + (100 - COALESCE(pa.appointment_completion_rate, 100)) * 0.2
            + CASE WHEN COALESCE(plv.days_since_last_appointment, 9999) > 180 THEN 10 ELSE 0 END
            + COALESCE(pr.referrals_last_6_months, 0) * 2
            + CASE WHEN pcc.age > 65 THEN 10 ELSE 0 END
        , 2) AS risk_score
    FROM patient_chronic_conditions pcc
        LEFT JOIN patient_medication_profile pmp ON pmp.patient_id = pcc.patient_id
        LEFT JOIN patient_activity pa ON pa.patient_id = pcc.patient_id
        LEFT JOIN patient_last_visit plv ON plv.patient_id = pcc.patient_id
        LEFT JOIN patient_referrals pr ON pr.patient_id = pcc.patient_id
)
SELECT
    patient_id, first_name, last_name, embg, age,
    chronic_diagnoses_count, severe_symptoms_count,
    allergy_count, max_allergy_severity, conflicting_medications,
    current_medications, polypharmacy_status,
    appointment_completion_rate, days_since_last_appointment,
    referrals_last_6_months,
    risk_score,
    CASE
        WHEN risk_score > 75 THEN 'CRITICAL'
        WHEN risk_score > 50 THEN 'HIGH'
        WHEN risk_score > 25 THEN 'MODERATE'
        ELSE 'LOW'
    END AS risk_category,
    DENSE_RANK() OVER (ORDER BY risk_score DESC) AS risk_rank
FROM patient_risk_scores
WHERE chronic_diagnoses_count > 0 OR allergy_count > 0 OR current_medications > 0
ORDER BY risk_rank, last_name, first_name;
}}}

=== Relational Algebra ===

{{{
Params ← {(lookback_months, risk_assessment_start)}

PatientRecords ←
π_{patient_id, record_id}
(patients p ⟕ (p.patient_id = mr.patient_id) medical_records mr)

PatientChronicConditions ←
γ
  patient_id := p.patient_id; first_name := p.first_name; last_name := p.last_name; embg := p.embg;
  age := YEAR(AGE(CURRENT_DATE, p.date_of_birth));
  chronic_diagnoses_count := COUNT_DISTINCT(d.diagnosis_id);
  severe_symptoms_count := COUNT_DISTINCT(CASE WHEN UPPER(mrs.severity) = 'SEVERE' THEN mrs.symptom_id END)
(
  (
    (patients p ⟕ (p.patient_id = d.patient_id) diagnosis d)
    ⟕ (p.patient_id = pr.patient_id) PatientRecords pr
  )
  ⟕ (pr.record_id = mrs.record_id) medical_record_symptoms mrs
)

PatientMedicationProfile ←
γ
  patient_id := p.patient_id;
  current_medications := COUNT_DISTINCT(pmr.prescription_id);
  polypharmacy_status :=
    CASE WHEN COUNT_DISTINCT(pmr.prescription_id) ≥ 5 THEN 'HIGH_POLYPHARMACY'
         WHEN COUNT_DISTINCT(pmr.prescription_id) ≥ 3 THEN 'MODERATE_POLYPHARMACY'
         ELSE 'LOW_POLYPHARMACY' END;
  allergy_count := COUNT_DISTINCT(pa.allergy_id);
  max_allergy_severity :=
    CASE WHEN COUNT_DISTINCT(CASE WHEN a.allergy_severity = 'CRITICAL' THEN pa.allergy_id END) > 0 THEN 'CRITICAL'
         WHEN COUNT_DISTINCT(CASE WHEN a.allergy_severity = 'HIGH' THEN pa.allergy_id END) > 0 THEN 'HIGH'
         WHEN COUNT_DISTINCT(CASE WHEN a.allergy_severity = 'MEDIUM' THEN pa.allergy_id END) > 0 THEN 'MEDIUM'
         WHEN COUNT_DISTINCT(pa.allergy_id) > 0 THEN 'LOW'
         ELSE 'NONE' END;
  conflicting_medications := COUNT_DISTINCT(pmr2.prescription_id)
(
  (
    (
      (
        (
          (
            (patients p ⟕ (p.patient_id = pr.patient_id) PatientRecords pr)
            ⟕ (pr.record_id = pmr.record_id) prescription_medical_records pmr
          )
          ⟕ (p.patient_id = pa.patient_id) patient_allergies pa
        )
        ⟕ (pa.allergy_id = a.allergy_id) allergies a
      )
      ⟕ (a.allergy_id = apr.allergy_id) allergy_prescription_restrictions apr
    )
    ⟕ (apr.restriction_id = restr.restriction_id) prescription_restriction restr
  )
  ⟕ (pr.record_id = pmr2.record_id ∧ pmr2.prescription_id = restr.prescription_id) prescription_medical_records pmr2
)

FilteredAppointments ←
π_{a.appointment_id, a.patient_id, a.status}
(
  σ (a.appointment_date ≥ p.risk_assessment_start ∧ a.appointment_date ≤ CURRENT_DATE)
  (appointments a × Params p)
)

PatientActivity ←
γ
  patient_id := p.patient_id;
  appointment_completion_rate := ROUND(100.0 * COUNT(fa.appointment_id) FILTER (fa.status = 'COMPLETED') / COUNT(fa.appointment_id), 2)
(
  patients p ⟕ (p.patient_id = fa.patient_id) FilteredAppointments fa
)

PatientLastVisit ←
γ
  patient_id;
  days_since_last_appointment := CURRENT_DATE - MAX(appointment_date)
(
  σ (status = 'COMPLETED' ∧ appointment_date ≤ CURRENT_DATE) (appointments)
)

PatientReferrals ←
γ
  patient_id := p.patient_id;
  referrals_last_6_months := COUNT_DISTINCT(CASE WHEN r.referral_date > CURRENT_DATE - 6 MONTHS THEN r.referral_id END)
(
  (patients p ⟕ (p.patient_id = pr.patient_id) PatientRecords pr)
  ⟕ (pr.record_id = r.record_id) referrals r
)

PatientRiskScores ←
π
  patient_id, first_name, last_name, embg, age, chronic_diagnoses_count, severe_symptoms_count,
  allergy_count := COALESCE(pmp.allergy_count, 0),
  max_allergy_severity := COALESCE(pmp.max_allergy_severity, 'NONE'),
  conflicting_medications := COALESCE(pmp.conflicting_medications, 0),
  current_medications := COALESCE(pmp.current_medications, 0),
  polypharmacy_status := COALESCE(pmp.polypharmacy_status, 'LOW_POLYPHARMACY'),
  appointment_completion_rate := COALESCE(pa.appointment_completion_rate, 100),
  days_since_last_appointment := COALESCE(plv.days_since_last_appointment, 9999),
  referrals_last_6_months := COALESCE(pr.referrals_last_6_months, 0),
  risk_score :=
    ROUND(
      LEAST(COALESCE(chronic_diagnoses_count, 0), 10) * 3
      + COALESCE(severe_symptoms_count, 0) * 4
      + CASE COALESCE(pmp.max_allergy_severity, 'NONE')
             WHEN 'CRITICAL' THEN 20 WHEN 'HIGH' THEN 10 WHEN 'MEDIUM' THEN 5 ELSE 0 END
      + CASE COALESCE(pmp.polypharmacy_status, 'LOW_POLYPHARMACY')
             WHEN 'HIGH_POLYPHARMACY' THEN 15 WHEN 'MODERATE_POLYPHARMACY' THEN 8 ELSE 0 END
      + COALESCE(pmp.conflicting_medications, 0) * 10
      + (100 - COALESCE(pa.appointment_completion_rate, 100)) * 0.2
      + CASE WHEN COALESCE(plv.days_since_last_appointment, 9999) > 180 THEN 10 ELSE 0 END
      + COALESCE(pr.referrals_last_6_months, 0) * 2
      + CASE WHEN age > 65 THEN 10 ELSE 0 END
    , 2)
(
  (
    (
      (PatientChronicConditions pcc
      ⟕ (pcc.patient_id = pmp.patient_id) PatientMedicationProfile pmp)
      ⟕ (pcc.patient_id = pa.patient_id) PatientActivity pa
    )
    ⟕ (pcc.patient_id = plv.patient_id) PatientLastVisit plv
  )
  ⟕ (pcc.patient_id = pr.patient_id) PatientReferrals pr
)

RiskCategorized ←
π
  *,
  risk_category :=
    CASE WHEN risk_score > 75 THEN 'CRITICAL'
         WHEN risk_score > 50 THEN 'HIGH'
         WHEN risk_score > 25 THEN 'MODERATE'
         ELSE 'LOW' END
(PatientRiskScores)

FilteredPatients ←
σ (chronic_diagnoses_count > 0 ∨ allergy_count > 0 ∨ current_medications > 0)
(RiskCategorized)

RankedPatients ←
rank_dense
  risk_rank := ORDER BY risk_score DESC
(FilteredPatients)

Result ←
τ risk_rank ASC, last_name ASC, first_name ASC
(RankedPatients)
}}}