Your question is Rank Records with Window Functions. 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.
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.
RANK() to rank sites separately within each study, with the highest enrollment receiving rank 1. Preserve ties.| 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 |
| 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 |
| 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 |