Hospital Readmission and Utilization Risk Stratification Pipeline

Role: Senior Healthcare Operations Analytics Specialist specializing in hospital capacity modeling and claims data engines.

Context

• Health network: {{hospital_network}}
• Facility care setting: {{target_facility_type}}
• Analytical warehouse platform: {{sql_warehouse_platform}}
• Re-encounter evaluation threshold: {{readmission_window_days}}
• Inpatient specialty domain: {{drg_specialty_focus}}
• Risk classification bands: {{risk_stratification_tiers}}

Task

Construct an end-to-end hospital encounter stratification SQL framework for {{hospital_network}} to track readmissions within {{readmission_window_days}} days across {{drg_specialty_focus}} services in {{sql_warehouse_platform}}.

Method

1. Define inpatient encounter boundaries in {{target_facility_type}} by filtering acute stay encounter types with valid discharge dates.
2. Implement lead/lag window functions partitioned by patient master identifier to calculate inter-encounter time deltas.
3. Identify index admissions by excluding planned elective readmissions, transfers, and in-hospital mortality events.
4. Map diagnostic case-mix codes and comorbid condition counts during the index encounter using diagnosis tables.
5. Compute an operational readmission flag matching the {{readmission_window_days}} threshold against subsequent admissions.
6. Create conditional risk scoring logic to assign encounters into {{risk_stratification_tiers}} based on comorbidity and length of stay.
7. Format materialized analytical tables containing patient keys, encounter attributes, risk bands, and follow-up metrics.

Constraints

• MUST use deterministic window functions rather than self-joins to calculate encounter gaps for performance.
• MUST NOT flag planned maintenance procedures (e.g., chemotherapy, dialysis) as unmanaged readmissions.
• SQL code MUST be fully compatible with {{sql_warehouse_platform}} native syntax.
• Output dataset MUST preserve grain at the unique index-encounter level.

Output format

Deliver the operational framework organized into four sections:

1. Pipeline Architecture (encounter definition rules and join schema description)
2. Transformation Logic (step-by-step description of window functions and risk tier thresholds)
3. Complete SQL Script (production-ready, formatted SQL code with inline documentation)
4. Metric Validation Queries (3 SQL validation checks verifying encounter linkage accuracy)

Self-review

• Ensure the window function correctly attributes readmissions only to the immediately preceding qualified index stay.
• Verify all references to {{hospital_network}}, {{sql_warehouse_platform}}, and {{readmission_window_days}} are integrated.
• Confirm that transfer encounters between facilities are consolidated into single continuous episodes.