Tell me about high-level SQL topics you’ve worked with, including how you approach writing and validating queries.
For the practical portion, write a query that identifies qualifying sites for active studies and demonstrates how you would validate the result. Include only sites with at least two completed enrollment events.
Output
- One row per qualifying site, with
study_name, site_name, total_participants, latest_event_date, and study_site_rank.
- Include only active studies and qualifying sites, ordered by
study_name, then study_site_rank, then site_id.
Output details
- Order
study_name, study_site_rank, and site_id ascending.
- Rank sites separately within each study by
total_participants descending, assigning equal ranks to ties and leaving gaps.
- Calculate
total_participants from all enrollment events, treating NULL participant_count values as 0.
- Calculate
latest_event_date as the maximum event date across all enrollment events.
