Per-enrollment ratio metric — SUM of a boolean flag divided by COUNT of rows in the same group. Practice float-promoted percentage calc and the canonical retention-team scorecard.
Site managers want a per-enrollment adherence scorecard for the weekly retention call. From fact_trial_visits, give me one row per enrollment_id with: enrollment_id, scheduled (count of visit rows), completed (sum of completed_flag), and adherence_pct (100.0 * completed / scheduled, rounded to 1 decimal). Sort by adherence_pct DESC, then enrollment_id ASC for stable ordering on ties. The retention team flags any enrollment under 80% as a check-in priority; we just need the ranked list.
| Column | Type | Key |
|---|---|---|
| visit_id | INT | PK |
| enrollment_id | INT | FK → fact_enrollments |
| scheduled_date | TEXT | |
| actual_date | TEXT | |
| visit_type | TEXT | |
| completed_flag | INT | |
| days_off_schedule | INT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Single-table aggregate, one row per enrollment. The scheduled count is the row count per enrollment; the completed count is a sum of the boolean flag — both come from the same GROUP BY.
Adherence is a ratio of the two. Force float division by making one operand a float (multiply by 100.0 with the decimal, not 100). Round to one decimal place for display.
Four output columns: enrollment id, scheduled, completed, adherence. Rank by adherence descending, with the enrollment id as the stable tiebreaker. Row count = number of distinct enrollments.