Your question is Optimize Slow Patient Queries. Start with the requirements and the four tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
How do you optimize a slow-running SQL query that processes millions of rows of patient records at Medpace?
Write a PostgreSQL query that returns the active patients in study 101, their latest completed visit through January 31, 2025, and their serious or critical adverse-event count during the preceding 30 days. The result should preserve eligible patients without matching visits or events.
site_id, site_name, patient_id, latest_completed_visit, serious_event_count, and site_ranksite_id, then patient_id| Column | Type | Description |
|---|---|---|
| site_idPK | INT | Clinical site identifier |
| study_id | INT | Study identifier |
| site_name | VARCHAR(80) | Clinical site name |
| Column | Type | Description |
|---|---|---|
| patient_idPK | INT | Patient identifier |
| study_id | INT | Study identifier |
| site_id | INT | Assigned clinical site |
| enrollment_status | VARCHAR(30) | Current enrollment status |
| Column | Type | Description |
|---|---|---|
| visit_idPK | INT | Visit identifier |
| patient_id | INT | Patient associated with the visit |
| visit_date | DATE | Scheduled or completed visit date |
| visit_status | VARCHAR(40) | Visit workflow status |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Adverse event identifier |
| patient_id | INT | Patient associated with the event |
| event_date | DATE | Date the event was recorded |
| severity | VARCHAR(20) | Event severity classification |