Your question is Popular Songs by Region. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Spotify's Analytics Engineering team uses stream events to monitor which songs are gaining traction in each market. The regional ranking should reflect recent listening activity while excluding streams outside the target market and reporting periods.
Write a PostgreSQL query to identify the top three songs streamed in Spotify's GB market during the 30-day period ending on 2025-02-01. Treat the period as inclusive of 2025-01-02 and exclusive of 2025-02-01.
stream_events to tracks and exclude events whose track does not exist in tracks.region_code = 'GB' and the specified 30-day period.| Column | Type | Description |
|---|---|---|
| track_idPK | INT | Unique Spotify track identifier |
| track_title | VARCHAR(200) | Song title |
| artist_name | VARCHAR(200) | Display artist name |
| Column | Type | Description |
|---|---|---|
| event_idPK | BIGINT | Unique stream event identifier |
| track_id | INT | Streamed track identifier |
| listener_id | INT | Listener identifier, nullable for unidentified listening |
| region_code | VARCHAR(10) | Listener market code |
| streamed_at | TIMESTAMP | Stream event timestamp |