Spotify: Long-Form Podcast Episodes Released in 2024

Easy
924.

Spotify: Long-Form Podcast Episodes Released in 2024

EasyBasicsDates

MySQL

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

ColumnType
episode_idint
show_namevarchar
episode_titlevarchar
released_ondate
duration_secint

Primary key: episode_id.

One row per published podcast episode; duration_sec is its running time in seconds.

Example

The rows must come back in the order shown.

Episode

episode_idshow_nameepisode_titlereleased_onduration_sec
1Founders UnfilteredBootstrapping in Pune2024-03-144210
2Cricket After HoursThe Perfect Yorker2024-05-023600
3The Daily ByteChips and Tariffs2024-05-033599
4History UntoldThe Silk Route2023-12-315400
5Startup ChaiPricing Your SaaS2025-01-013900
6Code and CoffeeRust for Beginners2024-01-014210
7Mindful MorningsBreath Work2024-12-317265

Output

episode_idshow_nameminutes
7Mindful Mornings121
1Founders Unfiltered70
6Code and Coffee70
2Cricket After Hours60
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
episode_idint
show_namevarchar
episode_titlevarchar
released_ondate
duration_secint

Primary key: episode_id.

One row per published podcast episode; duration_sec is its running time in seconds.

Examples

Example 1

Episode

episode_idshow_nameepisode_titlereleased_onduration_sec
1Founders UnfilteredBootstrapping in Pune2024-03-144210
2Cricket After HoursThe Perfect Yorker2024-05-023600
3The Daily ByteChips and Tariffs2024-05-033599
4History UntoldThe Silk Route2023-12-315400
5Startup ChaiPricing Your SaaS2025-01-013900
6Code and CoffeeRust for Beginners2024-01-014210
7Mindful MorningsBreath Work2024-12-317265

Output

episode_idshow_nameminutes
7Mindful Mornings121
1Founders Unfiltered70
6Code and Coffee70
2Cricket After Hours60

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_id

Another 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 →