Snaps Analysis - Problem

Given two tables Activities and Age, calculate the percentage of total time spent on sending and opening snaps for each age group.

Activities Table:

  • activity_id (int): Unique identifier for each activity
  • user_id (int): User who performed the activity
  • activity_type (enum): Either 'send' or 'open'
  • time_spent (decimal): Time spent on the activity

Age Table:

  • user_id (int): Unique user identifier
  • age_bucket (enum): Age group ('21-25', '26-30', '31-35')

Return the percentage rounded to 2 decimal places for each age group and activity type.

Table Schema

Activities
Column Name Type Description
activity_id PK int Unique identifier for each activity
user_id int User who performed the activity
activity_type enum Type of activity: 'send' or 'open'
time_spent decimal Time spent on the activity in minutes
Primary Key: activity_id
Age
Column Name Type Description
user_id PK int Unique user identifier
age_bucket enum Age group: '21-25', '26-30', '31-35'
Primary Key: user_id

Input & Output

Example 1 — Basic Percentage Calculation
Input Tables:
Activities
activity_id user_id activity_type time_spent
1 1 send 10.5
2 2 open 5
3 1 open 2.5
4 3 send 8
Age
user_id age_bucket
1 21-25
2 26-30
3 31-35
Output:
age_bucket activity_type percentage
21-25 open 9.62
21-25 send 40.38
26-30 open 19.23
31-35 send 30.77
💡 Note:

Total time spent across all activities is 26.0 minutes. The 21-25 age group spent 13.0 minutes total (10.5 sending + 2.5 opening), which represents 50% of total time. The percentages are calculated as (group_time / total_time) * 100 and rounded to 2 decimal places.
Example 2 — Single Activity Type per Age Group
Input Tables:
Activities
activity_id user_id activity_type time_spent
1 1 send 15
2 2 send 10
Age
user_id age_bucket
1 21-25
2 26-30
Output:
age_bucket activity_type percentage
21-25 send 60
26-30 send 40
💡 Note:

When all users perform only one type of activity (sending), the percentages still sum to 100% across all age groups. User 1 (21-25) spent 15 minutes out of 25 total (60%), and user 2 (26-30) spent 10 minutes out of 25 total (40%).

Constraints

  • 1 ≤ activity_id ≤ 1000
  • 1 ≤ user_id ≤ 100
  • activity_type is either 'send' or 'open'
  • 0.1 ≤ time_spent ≤ 1000.0
  • age_bucket is one of '21-25', '26-30', '31-35'

Visualization

Tap to expand
Snaps Analysis: Time Percentage by Age GroupActivitiesuser_idtypetime1send10.52open5.0Ageuser_idage_bucket121-25226-30JOINGROUP BYSUM() OVER()Result: Percentage by Age & Activityage_bucketactivity_typepercentage21-25send40.3826-30open19.23Key Formula:percentage = ROUND(100.0 * group_sum / total_sum, 2)Window function calculates total_sum across all groups
Understanding the Visualization
1
Input Tables
Activities table with time_spent and Age table with age_bucket
2
JOIN & GROUP
Join on user_id and group by age_bucket, activity_type
3
Calculate %
Use window function to get percentage of total time
Key Takeaway
🎯 Key Insight: Use window functions to calculate percentages efficiently in a single query without subqueries

Related Problems

Asked in
Snapchat 12 Meta 8 TikTok 6
23.4K Views
Medium Frequency
~12 min Avg. Time
890 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen