Playlist Duration

ASKED IN INTERVIEW

2pts

Spotify

Problem Statement

Spotify users want to know exactly how long their playlists are before starting a commute. You are given a table of tracks associated with various playlists.

Write an SQL query to calculate the total duration of playlist_id 500. The result should show the total time in separate columns for minutes and seconds.

Rules:

Target playlist_id 500.
Total seconds should be the remainder after minutes are calculated (e.g., 65 seconds = 1 minute and 5 seconds).
Output columns: playlist_id, total_minutes, total_seconds.
Results should be sorted by total_minutes in descending order.

Table Schema:

PlaylistTracks: playlist_id, track_id, duration_seconds.

Tests your understanding of

Basic SQL, Aggregation, Mathematics and Formatting

Input Tables

PlaylistTracks

playlist_id(INTEGER)	track_id(INTEGER)	duration_seconds(INTEGER)
500	1	180
500	2	210
500	3	45
501	4	300
500	5	120
500	6	200

Expected Output

playlist_id(INTEGER)	total_minutes(INTEGER)	total_seconds(INTEGER)
500	12	35