Two-CTE pattern: numerator (SAE counts) and denominator (enrollment counts) computed separately, joined on site_id, with float-promoted ratio. Practice the canonical 'rate per X' shape that drives every safety, quality, and risk-adjusted comparison report.
Pharmacovigilance Q2 report needs SAE rate per site. For every site in dim_sites, count serious AEs (serious_flag = 1) and divide by total enrollments at that site. Output site_name, saes (with COALESCE to 0 for sites with no SAEs), enroll_count, and sae_rate_pct (100.0 * saes / enroll_count, rounded to 1 decimal). Sort by sae_rate_pct DESC, site_name ASC. Use a two-CTE pattern — one for SAE counts, one for enrollment totals — then LEFT JOIN sae onto the site list so sites with zero SAEs still show up at 0%. Path is fact_adverse_events → fact_enrollments (on enrollment_id) for the SAE site attribution.
| Column | Type | Key |
|---|---|---|
| ae_id | INT | PK |
| enrollment_id | INT | FK → fact_enrollments |
| event_date | TEXT | |
| meddra_term | TEXT | |
| severity | INT | |
| serious_flag | INT | |
| related_to_intervention | TEXT | |
| outcome | 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 |
| Column | Type | Key |
|---|---|---|
| site_id | INT | PK |
| site_name | TEXT | |
| city | TEXT | |
| state | TEXT | |
| region | TEXT | |
| site_type | TEXT | |
| enrollment_capacity | INT | |
| pi_name | TEXT | |
| activated_date | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Two-CTE pattern. First CTE = SAE numerator per site (filter serious_flag, count, group by site). Second CTE = enrollment denominator per site (group by site, count). The CTEs reach the site through the enrollment table; AEs don't have a direct site FK.
Final SELECT joins the site dimension to the totals CTE (inner) and the SAE CTE (LEFT — zero-SAE sites still need to appear). Use COALESCE on the SAE count to render NULL as 0.
Float-promote the percentage. Sort by rate descending with site name as the alphabetic tiebreaker. One row per site in the dimension.