Playlist Length as Clock Time — SQL Strings Problem
The playlist header shows how many tracks a playlist holds and how long it plays, written as a clock: H:MM:SS, where the hours are not padded and the…
- Difficulty: Medium
- Topics: Strings, Aggregation, Joins
- Dialect: MySQL
- Problem: #488
Problem statement
The playlist header shows how many tracks a playlist holds and how long it plays, written as a clock: H:MM:SS, where the hours are not padded and the minutes and seconds always have two digits (3,725 seconds is 1:02:05, 59 seconds is 0:00:59).
Return every playlist, including empty ones, with the columns playlist_name, track_count and total_length (an empty playlist is 0:00:00). Return the rows in any order.
Tables
Table: Playlist
| Column | Type |
|---|---|
| playlist_id | int |
| playlist_name | varchar |
| owner_id | int |
Primary key: playlist_id.
One row per playlist.
Table: Track
| Column | Type |
|---|---|
| track_id | int |
| title | varchar |
| duration_secs | int |
Primary key: track_id.
One row per track; duration_secs is its length in whole seconds.
Table: PlaylistTrack
| Column | Type |
|---|---|
| playlist_id | int |
| position | int |
| track_id | int |
Primary key: playlist_id, position.
The tracks of a playlist in order; the same track may appear twice in one playlist, and each appearance plays.
Examples
Example 1
Playlist
| playlist_id | playlist_name | owner_id |
|---|---|---|
| 1 | Monsoon Chill | 41 |
| 2 | Gym Hip-Hop | 42 |
| 3 | Long Drive | 41 |
| 4 | Sleep Sounds | 43 |
Track
| track_id | title | duration_secs |
|---|---|---|
| 101 | Baarish | 214 |
| 102 | Safar | 198 |
| 103 | Raat | 1800 |
| 104 | Jugnu | 59 |
PlaylistTrack
| playlist_id | position | track_id |
|---|---|---|
| 1 | 1 | 101 |
| 1 | 2 | 102 |
| 2 | 1 | 104 |
| 3 | 1 | 103 |
| 3 | 2 | 103 |
| 3 | 3 | 101 |
Output
| playlist_name | track_count | total_length |
|---|---|---|
| Monsoon Chill | 2 | 0:06:52 |
| Gym Hip-Hop | 1 | 0:00:59 |
| Long Drive | 3 | 1:03:34 |
| Sleep Sounds | 0 | 0:00:00 |
How to solve Playlist Length as Clock Time
Split the job in two. Aggregate: LEFT JOIN Playlist to PlaylistTrack and on to Track, then group by playlist; COUNT(pt.track_id) counts appearances (0 for an empty playlist, because COUNT of a column skips NULL) and SUM(duration_secs) totals the seconds, wrapped in COALESCE(…, 0) since the sum over no rows is NULL. A track listed twice is counted and timed twice, as the statement says.
Format: integer division and remainder break the seconds into a clock. secs DIV 3600 is the hours; secs DIV 60 - hours * 60 the minutes within the hour; secs - (secs DIV 60) * 60 the seconds (MySQL's MOD(secs, 60) says the same). LPAD(…, 2, '0') gives the two-digit fields, and CONCAT joins them with colons. The hours are left as they are, so 36,000 seconds read 10:00:00.
RIGHT(CONCAT('0', x), 2) is an older padding trick that works for values under 100. Using DIV rather than / matters: / is decimal division in MySQL and would print 1.0347 hours. The cost is one pass over the playlist entries.
Reference solution (MySQL)
WITH totals AS (
SELECT p.playlist_id, p.playlist_name, COUNT(pt.track_id) AS track_count,
COALESCE(SUM(t.duration_secs), 0) AS secs
FROM Playlist p
LEFT JOIN PlaylistTrack pt ON pt.playlist_id = p.playlist_id
LEFT JOIN Track t ON t.track_id = pt.track_id
GROUP BY p.playlist_id, p.playlist_name
)
SELECT playlist_name, track_count,
CONCAT(secs DIV 3600, ':', LPAD(secs DIV 60 - (secs DIV 3600) * 60, 2, '0'), ':', LPAD(secs - (secs DIV 60) * 60, 2, '0')) AS total_length
FROM totalsAnother way
SELECT p.playlist_name, COALESCE(s.n, 0) AS track_count,
CONCAT(COALESCE(s.secs, 0) DIV 3600, ':', RIGHT(CONCAT('0', (COALESCE(s.secs, 0) DIV 60 - (COALESCE(s.secs, 0) DIV 3600) * 60)), 2), ':', RIGHT(CONCAT('0', COALESCE(s.secs, 0) - (COALESCE(s.secs, 0) DIV 60) * 60), 2)) AS total_length
FROM Playlist p LEFT JOIN (
SELECT pt.playlist_id, COUNT(*) AS n, SUM(t.duration_secs) AS secs FROM PlaylistTrack pt JOIN Track t ON t.track_id = pt.track_id GROUP BY pt.playlist_id
) s ON s.playlist_id = p.playlist_id← Titles Licensed in Every Region on Launch Day · Sign-Up Channels by First-Week Activation →