Send/Open Snap Rate
ASKED IN INTERVIEWSnap
Problem Statement
Snap wants to analyze how users interact with the app. Calculate the percentage of time spent sending vs. opening snaps for each user.
Rules:
- Output columns: user_id, send_perc, open_perc.
- send_perc = (Time spent sending / (Time spent sending + Time spent opening)) * 100.
- open_perc = (Time spent opening / (Time spent sending + Time spent opening)) * 100.
- Round results to 2 decimal places.
- Only include users who have performed at least one of these two activities.
- Results must be sorted by user_id in ascending order.
Tests your understanding of
Common Table Expressions, Conditional Aggregation and Mathematical Operations
Input Tables
activities
| activity_id(INTEGER) | user_id(INTEGER) | activity_type(VARCHAR) | time_spent(DECIMAL) |
|---|---|---|---|
| 1 | 123 | send | 4.5 |
| 2 | 123 | open | 3 |
| 3 | 456 | send | 5 |
| 4 | 456 | open | 5 |
| 5 | 123 | send | 2.5 |
| 6 | 789 | chat | 10 |
| 7 | 789 | open | 2 |
Expected Output
| user_id(INTEGER) | send_perc(DECIMAL) | open_perc(DECIMAL) |
|---|---|---|
| 123 | 70 | 30 |
| 456 | 50 | 50 |
| 789 | 0 | 100 |
Tags
MediumASKED IN INTERVIEWCommon Table ExpressionsConditional AggregationMathematical Operations
20-25 min
42%