Second Largest Element in SQL | Dataford Interview Questions

Your question is Second Largest Element in SQL. Start with the requirements and the three 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.

Problem

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.

Schema

studies

Column	Type	Description
study_idPK	INTEGER	Study identifier
study_code	VARCHAR(30)	IQVIA study code
therapeutic_area	VARCHAR(60)	Therapeutic area
status	VARCHAR(20)	Current study status

sites

Column	Type	Description
site_idPK	VARCHAR(20)	Trial site identifier
study_id	INTEGER	Related study identifier
site_name	VARCHAR(100)	Trial site name

enrollment_events

Column	Type	Description
event_idPK	INTEGER	Enrollment event identifier
site_id	VARCHAR(20)	Related trial site
enrolled_patients	INTEGER	Patients enrolled during the event