Given tables for user activity and age groups, show what percentage of each age group's total time was spent sending snaps vs opening snaps. Exclude other activity types. Round to 2 decimal places.
Schema
activities
activity_idINTEGER
user_idINTEGER
typeVARCHAR
time_spentFLOAT
activity_dateDATETIME
type values: 'send', 'open', 'chat'
age_breakdown
user_idINTEGER
age_bucketVARCHAR
age_bucket values: '21-25', '26-30', '31-35', '36-40', '41-45', '46-50'
Target output
Target output
| age_bucket | send_perc | open_perc |
|---|
| 21-25 | 58.57 | 41.43 |
| 26-30 | 77.78 | 22.22 |
What this question tests
Tests conditional aggregation: splitting one activity column into two percentages with CASE inside an aggregate, joined against a dimension table. This shape appears constantly in product analytics, where an event log holds many activity types and the interviewer wants shares per segment, not raw counts.
More SQL interview questions
