On-ramp to Medium tier: a three-table INNER JOIN is the only new mechanic over the Easy missions — no window functions, no CTEs. Practice navigating dim → bridge dim → fact, applying a WHERE clause on the parent dim, and aggregating on the fact, with a stable two-key sort. This is the canonical 'roster' shape that every Medium/Hard mission later builds on.
Steering committee wants a phase-3-only roster: every phase-3 trial, every arm in that trial, and how many patients are on each arm. From dim_trials WHERE phase = 3, joined to dim_protocols on trial_id, joined to fact_enrollments on protocol_id (and trial_id, to be safe). Output trial_name, arm_name, enrolled — sorted by trial_name then arm_name so the roster reads cleanly down the page. Don't filter on enrollment status; we want every randomized patient in the count.
| Column | Type | Key |
|---|---|---|
| trial_id | INT | PK |
| nct_id | TEXT | |
| trial_name | TEXT | |
| phase | INT | |
| condition | TEXT | |
| therapeutic_area | TEXT | |
| sponsor | TEXT | |
| status | TEXT | |
| start_date | TEXT | |
| end_date | TEXT |
| Column | Type | Key |
|---|---|---|
| protocol_id | INT | PK |
| trial_id | INT | FK → dim_trials |
| arm_name | TEXT | |
| intervention_type | TEXT | |
| atc_class | TEXT | |
| version | TEXT |
| Column | Type | Key |
|---|---|---|
| enrollment_id | INT | PK |
| trial_id | INT | FK → dim_trials |
| protocol_id | INT | FK → dim_protocols |
| patient_id | INT | FK → dim_trial_patients |
| site_id | INT | FK → dim_sites |
| enrolled_date | TEXT | |
| status | TEXT | |
| withdrawal_reason | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Three-table inner join through trials → protocols → enrollments. Filter on the trial's phase column at the parent dimension.
Group by the trial name and the arm name; aggregate is a row count per (trial, arm) combination. Name it for the metric.
Sort alphabetically on trial then arm so the roster reads down the page. Six phase-3 trials with two-to-three arms each gives a row count in the low double digits.