Percentage of Athletes Who Ran in a Given Month
Problem statement
A running app tracks its athletes and every run they log. The product team wants a monthly engagement number: what percentage of all athletes logged at least one run in March 2026?
Tables (SQLite dialect, dates as 'YYYY-MM-DD' text):
runs(athlete_id INT, run_id INT, run_date TEXT, run_time INT): one row per logged run;run_timeis the run's duration in seconds. An athlete can log many runs.athletes(athlete_id INT, join_date TEXT, age INT): one row per athlete;athlete_idis unique.
Return one row with one column, pct_active: the percentage (0 to 100) of all athletes in athletes who logged at least one run from 2026-03-01 through 2026-03-31. Runs by ids that are not in athletes do not count. Rounding to 2 or more decimals is fine. If athletes is empty, return NULL.
Example
athletes: ids 1 to 8 (join dates and ages omitted here; athlete 5 joined on 2026-03-20).
runs:
athlete_id | run_id | run_date | run_time
1 | 501 | 2026-03-02 | 1820
1 | 502 | 2026-03-09 | 1795
1 | 503 | 2026-03-30 | 2410
2 | 504 | 2026-02-28 | 1500
3 | 505 | 2026-03-31 | 3600
4 | 506 | 2026-04-01 | 1900
9 | 507 | 2026-03-15 | 2000
Output:
pct_active
25.0
Athletes 1 and 3 ran in March (athlete 1 three times, counted once), so 2 of 8 athletes = 25%. Athlete 9 is not in athletes, and the runs on February 28 and April 1 fall outside the month.
Follow-up (discussion, not graded): is "all athletes" the right denominator for a monthly engagement metric?
Show hint
Which table defines who belongs in the denominator, and should the query start from it?
Relevant roles: Data Scientist, Forward Deployed Engineer and Applied Scientist.