Spotify: Long-Form Podcast Episodes Released in 2024
Spotify's podcast ad sales team sells a separate package for long-form shows and needs the list of long episodes published last year.
- Difficulty: Easy
- Topics: Basics, Dates
- Dialect: MySQL
- Problem: #924
Problem statement
Spotify's podcast ad sales team sells a separate package for long-form shows and needs the list of long episodes published last year.
Return every episode released in 2024 that runs at least one hour (duration_sec of 3600 or more), with the columns episode_id, show_name and minutes — the duration in whole minutes, any leftover seconds dropped. Order by duration_sec from longest to shortest, then by episode_id.
A practice scenario set at Spotify: the tables and data are invented, and this is not a question from, or endorsed by, Spotify.
Tables
Table: Episode
| Column | Type |
|---|---|
| episode_id | int |
| show_name | varchar |
| episode_title | varchar |
| released_on | date |
| duration_sec | int |
Primary key: episode_id.
One row per published podcast episode; duration_sec is its running time in seconds.
Examples
Example 1
Episode
| episode_id | show_name | episode_title | released_on | duration_sec |
|---|---|---|---|---|
| 1 | Founders Unfiltered | Bootstrapping in Pune | 2024-03-14 | 4210 |
| 2 | Cricket After Hours | The Perfect Yorker | 2024-05-02 | 3600 |
| 3 | The Daily Byte | Chips and Tariffs | 2024-05-03 | 3599 |
| 4 | History Untold | The Silk Route | 2023-12-31 | 5400 |
| 5 | Startup Chai | Pricing Your SaaS | 2025-01-01 | 3900 |
| 6 | Code and Coffee | Rust for Beginners | 2024-01-01 | 4210 |
| 7 | Mindful Mornings | Breath Work | 2024-12-31 | 7265 |
Output
| episode_id | show_name | minutes |
|---|---|---|
| 7 | Mindful Mornings | 121 |
| 1 | Founders Unfiltered | 70 |
| 6 | Code and Coffee | 70 |
| 2 | Cricket After Hours | 60 |
How to solve Spotify: Long-Form Podcast Episodes Released in 2024
This is a single-table filter with a computed column. Two conditions select the rows: the release date falls in 2024, and duration_sec >= 3600. The comparison must be >= — an episode of exactly one hour is long-form, while 3599 seconds is not.
The year test can be written as a range, released_on BETWEEN '2024-01-01' AND '2024-12-31', which keeps both New Year's Day and New Year's Eve, or as YEAR(released_on) = 2024. The range is the one an index on released_on can use.
Minutes are whole minutes, so leftover seconds are dropped rather than rounded: 4210 seconds is 70 minutes, not 70.17 or 71. In MySQL / returns a decimal, so use DIV (integer division) or wrap the quotient in FLOOR. Finally the order: the statement sorts by the exact duration, so two episodes that both show 70 minutes but differ in seconds keep their true order, and episode_id breaks exact ties. One scan of the table plus a sort of the few qualifying rows.
Reference solution (MySQL)
SELECT episode_id, show_name, duration_sec DIV 60 AS minutes
FROM Episode
WHERE released_on BETWEEN '2024-01-01' AND '2024-12-31'
AND duration_sec >= 3600
ORDER BY duration_sec DESC, episode_idAnother way
SELECT episode_id, show_name, FLOOR(duration_sec / 60) AS minutes FROM Episode WHERE YEAR(released_on) = 2024 AND NOT duration_sec < 3600 ORDER BY duration_sec DESC, episode_id← Spotify: Explicit Tracks Sitting on Kids Playlists · Spotify: Skips and Full Plays per Track →