IQVIA's Clinical Trial Management System needs to identify site enrollment performance across active studies. Write a PostgreSQL query that finds the sites with the second-highest distinct total enrollment within each active study.
Requirements
- Include only studies whose status is
Active.
- Calculate each site's total enrolled patients across all enrollment events, treating missing event totals as zero.
- Return every site tied at the second-highest distinct total for its study, ordered by
study_id and site_id.
Use a window function such as RANK() so ties at the highest value do not incorrectly exclude the second distinct value.
