TikTok: Remix Velocity
Tik Tok, Instagram, Snapchat
Problem Statement
TikTok wants to track how quickly a sound goes viral. The Remix Velocity of a sound is measured by the number of videos created using that sound within exactly 1 hour of the original video being posted.
Write a query to rank sounds by their Remix Velocity.
Rules:
- The Original Video is the first video (earliest created_at) that uses a specific sound_id.
- A Remix is any subsequent video using that same sound_id.
- Only count remixes that were posted within 1 hour (<= 60 minutes) of the original video’s created_at time.
- Sounds with the same remix count should have the same rank (use DENSE_RANK).
- Output columns: sound_id, remix_count, velocity_rank.
- Sort by velocity_rank ascending, then sound_id ascending.
Tests your understanding of
Self Joins, Ranking, Date Arithmetic and Filtering
Input Tables
videos
| video_id(INTEGER) | sound_id(INTEGER) | created_at(TIMESTAMP) |
|---|---|---|
| 1 | 501 | 2026-03-01 10:00:00 |
| 2 | 501 | 2026-03-01 10:15:00 |
| 3 | 501 | 2026-03-01 10:45:00 |
| 4 | 501 | 2026-03-01 11:30:00 |
| 5 | 502 | 2026-03-01 12:00:00 |
| 6 | 502 | 2026-03-01 12:05:00 |
| 7 | 503 | 2026-03-01 14:00:00 |
| 8 | 503 | 2026-03-01 16:00:00 |
Expected Output
| sound_id(INTEGER) | remix_count(INTEGER) | velocity_rank(INTEGER) |
|---|---|---|
| 501 | 2 | 1 |
| 502 | 1 | 2 |
| 503 | 0 | 3 |
Tags
HardSelf JoinsRankingDate ArithmeticFiltering
25-35 min
32%