Playlist Length as Clock Time

Medium
488.

Playlist Length as Clock Time

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

ColumnType
playlist_idint
playlist_namevarchar
owner_idint

Primary key: playlist_id.

One row per playlist.

Table: Track

ColumnType
track_idint
titlevarchar
duration_secsint

Primary key: track_id.

One row per track; duration_secs is its length in whole seconds.

Table: PlaylistTrack

ColumnType
playlist_idint
positionint
track_idint

Primary key (together): playlist_id, position.

The tracks of a playlist in order; the same track may appear twice in one playlist, and each appearance plays.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Playlist

playlist_idplaylist_nameowner_id
1Monsoon Chill41
2Gym Hip-Hop42
3Long Drive41
4Sleep Sounds43

Track

track_idtitleduration_secs
101Baarish214
102Safar198
103Raat1800
104Jugnu59

PlaylistTrack

playlist_idpositiontrack_id
11101
12102
21104
31103
32103
33101

Output

playlist_nametrack_counttotal_length
Monsoon Chill20:06:52
Gym Hip-Hop10:00:59
Long Drive31:03:34
Sleep Sounds00:00:00
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.

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

ColumnType
playlist_idint
playlist_namevarchar
owner_idint

Primary key: playlist_id.

One row per playlist.

Table: Track

ColumnType
track_idint
titlevarchar
duration_secsint

Primary key: track_id.

One row per track; duration_secs is its length in whole seconds.

Table: PlaylistTrack

ColumnType
playlist_idint
positionint
track_idint

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_idplaylist_nameowner_id
1Monsoon Chill41
2Gym Hip-Hop42
3Long Drive41
4Sleep Sounds43

Track

track_idtitleduration_secs
101Baarish214
102Safar198
103Raat1800
104Jugnu59

PlaylistTrack

playlist_idpositiontrack_id
11101
12102
21104
31103
32103
33101

Output

playlist_nametrack_counttotal_length
Monsoon Chill20:06:52
Gym Hip-Hop10:00:59
Long Drive31:03:34
Sleep Sounds00: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 totals

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