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.
Output
- One row per active study-101 patient, with
site_id, site_name, patient_id, latest_completed_visit, serious_event_count, and site_rank
- Include patients with no qualifying visit or event, representing missing values appropriately
- Rank patients within each site by serious-event count descending, then patient ID ascending
- Order the final rows by
site_id, then patient_id
