Aggregate on a boolean (0/1) flag column. Practice GROUP BY on an INTEGER flag, COUNT(*) per bucket, and the framing of completion/adherence reports — the daily heartbeat metric for any patient-retention team.
Quick one for the morning standup. From fact_trial_visits, count visits grouped by completed_flag — completed_flag and visit_count, sorted ascending so the no-shows (flag=0) appear above the completed (flag=1). Two rows out. We track the ratio every morning and flag any week where the no-show share creeps above 10% — that's the early warning for protocol-level retention problems.
| 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 over the entire visit log — no WHERE filter.
completed_flag is stored as INTEGER 0/1, not a string. Group on it.
Two output columns: the flag value and a row count named for the metric. Sort ascending so the flag-0 (no-show) bucket appears first.