Changes between Initial Version and Version 1 of AdvancedReports

Timestamp:

08/29/26 13:43:39 (12 days ago)

Author:

236021

Comment:

--

AdvancedReports

@@ +1 @@
+= Advanced Reports =
+
+
+----
+
+== Doctor performance analytics ==
+
+This report identifies which doctors are performing best over a given date range (`report_start`–`report_end`), combining several independent activity streams into one weighted score:
+
+* Appointment volume and completion rate.
+* Procedure volume, revenue, and how often a performed procedure has a documented result on file.
+* Lab test volume, revenue, and how often an ordered test type has a result available.
+* Referral volume and whether referred patients actually followed up with the receiving doctor.
+* Breadth of care, measured as the number of distinct patients touched across procedures, lab tests, and appointments combined.
+
+Six factors feed into one weighted performance_score: appointment completion,
+procedure documentation, lab result availability, and referral follow-up each
+contribute a percentage-based rate, while procedure volume and patient breadth
+contribute capped counts (so one very busy doctor can't dominate the score). Doctors
+are then ranked with DENSE_RANK(), so tied scores share a rank instead of skipping
+numbers.
+
+
+=== SQL ===
+
+{{{
+WITH params AS (
+    SELECT
+        CAST('2026-05-16' AS DATE) AS report_start,
+        CAST('2026-08-26' 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(*) AS total_appointments,
+        COUNT(*) FILTER (WHERE a.status = 'COMPLETED') AS completed_appointments,
+        COUNT(*) FILTER (WHERE a.status = 'CANCELLED') AS cancelled_appointments,
+        ROUND(100.0 * COUNT(*) FILTER (WHERE a.status = 'COMPLETED') / NULLIF(COUNT(*), 0), 2) AS appointment_completion_rate
+    FROM doctors d
+        JOIN appointments a ON d.doctor_id = a.doctor_id
+        JOIN doctor_specialization ds ON d.specialization_id = ds.specialization_id
+        JOIN departments dept ON d.department_id = dept.department_id
+        JOIN params p ON 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(DISTINCT dpi.procedure_id) / NULLIF(COUNT(DISTINCT pp.procedure_id), 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
+        ) 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.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
+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(*);
+  completed_appointments := COUNT(*) FILTER (a.status = 'COMPLETED');
+  cancelled_appointments := COUNT(*) FILTER (a.status = 'CANCELLED');
+  appointment_completion_rate := ROUND(100.0 * COUNT(*) FILTER (a.status = 'COMPLETED') / COUNT(*), 2)
+(
+  σ (a.appointment_date ≥ p.report_start ∧ a.appointment_date ≤ p.report_end)
+  (
+    (
+      (doctors d ⨝ (d.doctor_id = a.doctor_id) appointments a)
+      ⨝ (d.specialization_id = ds.specialization_id) doctor_specialization ds
+    )
+    ⨝ (d.department_id = dept.department_id) departments dept
+    × Params p
+  )
+)
+
+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_DISTINCT(dpi.procedure_id) / COUNT_DISTINCT(pp.procedure_id), 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)
+  (
+    (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, appointment_completion_rate,
+  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(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
+)
+
+RankedDoctors ←
+rank_dense
+  performance_rank := ORDER BY performance_score DESC
+(DoctorScores)
+
+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.
+* A direct medication safety flag counts how many of the patient's currently prescribed medications are linked, via `allergy_prescription_restrictions`, to one of their documented allergies. This is the strongest single signal in the score, worth 10 points per conflict.
+* Polypharmacy tier reflects how many distinct medications the patient currently has on record, bucketed into `LOW_POLYPHARMACY`, `MODERATE_POLYPHARMACY`, or `HIGH_POLYPHARMACY`.
+* Appointment adherence covers the completion rate and days since the last appointment, within the lookback window set by the `:lookback_months` parameter.
+* 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.
+
+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 pa.allergy_id) > 0 THEN 'MEDIUM'
+            ELSE 'LOW'
+        END AS max_allergy_severity,
+        COUNT(DISTINCT restr.restriction_id) FILTER (WHERE pmr2.prescription_id IS NOT NULL)
+            AS conflicting_restrictions_on_current_meds
+    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,
+        CURRENT_DATE - MAX(a.appointment_date) AS days_since_last_appointment
+    FROM patients p
+        LEFT JOIN appointments a
+            ON a.patient_id = p.patient_id
+            AND a.appointment_date >= (SELECT risk_assessment_start FROM params)
+    GROUP BY p.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, 'LOW') AS max_allergy_severity,
+        COALESCE(pmp.conflicting_restrictions_on_current_meds, 0) AS conflicting_restrictions_on_current_meds,
+        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(pa.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, 'LOW')
+                  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_restrictions_on_current_meds, 0) * 10
+            + (100 - COALESCE(pa.appointment_completion_rate, 100)) * 0.2
+            + CASE WHEN COALESCE(pa.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_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_restrictions_on_current_meds,
+    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(pa.allergy_id) > 0 THEN 'MEDIUM' ELSE 'LOW' END;
+  conflicting_restrictions_on_current_meds :=
+    COUNT_DISTINCT(restr.restriction_id) FILTER (pmr2.prescription_id IS NOT NULL)
+(
+  (
+    (
+      (
+        (
+          (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_date ≥ p.risk_assessment_start) (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);
+  days_since_last_appointment := CURRENT_DATE - MAX(fa.appointment_date)
+(
+  patients p ⟕ (p.patient_id = fa.patient_id) FilteredAppointments fa
+)
+
+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, 'LOW'),
+  conflicting_restrictions_on_current_meds := COALESCE(pmp.conflicting_restrictions_on_current_meds, 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(pa.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, 'LOW')
+             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_restrictions_on_current_meds, 0) * 10
+      + (100 - COALESCE(pa.appointment_completion_rate, 100)) * 0.2
+      + CASE WHEN COALESCE(pa.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 = 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)
+}}}