Rank Records with Window Functions | Dataford Interview Questions

Bristol Myers Squibb clinical operations teams need to compare site enrollment performance across active studies for products such as Opdivo and Eliquis. Write a PostgreSQL query that ranks each participating site within its study.

Requirements

Join the study, site, and enrollment data.
Include only active studies where the site enrollment count is known.
Use RANK() to rank sites separately within each study, with the highest enrollment receiving rank 1. Preserve ties.
Return the results ordered by study and rank, with site ID as a deterministic tie-breaker.

Schema

clinical_trials

Column	Type	Description
trial_idPK	INT	Unique clinical trial identifier
product_name	VARCHAR(100)	Bristol Myers Squibb product under study
therapeutic_area	VARCHAR(80)	Therapeutic area for the study
status	VARCHAR(20)	Current study status

trial_sites

Column	Type	Description
site_idPK	INT	Unique clinical trial site identifier
site_name	VARCHAR(120)	Site or institution name
country	VARCHAR(60)	Country where the site operates

site_enrollment

Column	Type	Description
enrollment_idPK	INT	Unique enrollment record identifier
trial_id	INT	Referenced clinical trial
site_id	INT	Referenced trial site
enrolled_patients	INT	Number of enrolled patients

Your question is Rank Records with Window Functions. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem