Get hands-on with dim_trials — the trial registry that anchors every clinical research SQL query. Practice basic single-table SELECT, WHERE filtering on a status column, GROUP BY with COUNT(*), and ORDER BY before joins enter the picture.
Welcome aboard. Before our portfolio review with the steering committee tomorrow, I need a quick read on our current pipeline. From dim_trials, give me a count of how many ACTIVE trials we have at each phase — phase 2 vs phase 3 — sorted by phase ascending. The status column is a TEXT field; active trials use the literal value 'Active' (capital A). This is the headline number on slide 1 of every portfolio meeting; getting the shape of dim_trials in your fingers now will save you in every mission downstream.
| 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 |
Each hint you reveal reduces the XP you can earn. Try the query first.
Single-table aggregate. dim_trials is the only table you need; group by a category and count.
The status column is TEXT and case-sensitive — the active value matches the briefing's exact capitalization.
Two output columns: the grouping dimension and the count. Name the count column so the slide template recognizes it. Sort ascending on the dimension.