Story Completion
ASKED IN INTERVIEWSnap
Problem Statement
Snap engineers want to measure "Story Completion Rate". A user completes a story if the number of unique snaps they viewed for that story equals the story's total_snaps.
Write an SQL query to calculate the percentage of users who have completed at least one story they started.
Rules:
- Completion Rate = (Users who completed at least one story / Users who viewed at least one snap) * 100.
- Round the result to 2 decimal places.
- Only consider users who appear in the Story_Views table.
- Output columns: completion_rate.
- Result should be a single row.
Tests your understanding of
Aggregation, Ratios and Joins
Input Tables
Stories
| story_id(INTEGER) | total_snaps(INTEGER) |
|---|---|
| 101 | 3 |
| 102 | 2 |
Story_Views
| user_id(INTEGER) | story_id(INTEGER) | snap_index(INTEGER) |
|---|---|---|
| 1 | 101 | 1 |
| 1 | 101 | 2 |
| 1 | 101 | 3 |
| 2 | 101 | 1 |
| 3 | 102 | 1 |
Expected Output
| completion_rate(DECIMAL) |
|---|
| 33.33 |
Tags
MediumASKED IN INTERVIEWAggregationRatiosJoins
15-20 min
62%