Watch Hours by Genre

Easy
475.

Watch Hours by Genre

Content acquisition decides next year's budget by genre. Using every watch session, compute the total time watched per genre of the title watched, in hours.

Return the columns genre and watch_hours, where watch_hours is the sum of watched_minutes divided by 60 and rounded to 2 decimal places. Genres with no sessions do not appear. Order the rows by watch_hours from highest to lowest, and by genre alphabetically when two genres tie.

Tables

Table: Title

ColumnType
title_idint
title_namevarchar
genrevarchar

Primary key: title_id.

One row per title; every title has exactly one primary genre.

Table: WatchSession

ColumnType
session_idint
profile_idint
title_idint
watched_minutesint

Primary key: session_id.

One row per playback session; title_id always names a row of Title.

Example

The rows must come back in the order shown.

Title

title_idtitle_namegenre
1Coastal CopsThriller
2BackbenchersComedy
3Silk Road StoriesDocumentary
4Gully KingsComedy
5Desert RoseRomance

WatchSession

session_idprofile_idtitle_idwatched_minutes
11150
22225
31445
43120
53370
622100

Output

genrewatch_hours
Comedy2.83
Documentary1.17
Thriller1.17
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.

Watch Hours by Genre — SQL Joins Problem

Content acquisition decides next year's budget by genre. Using every watch session, compute the total time watched per genre of the title watched, in hours.

  • Difficulty: Easy
  • Topics: Joins, Aggregation
  • Dialect: MySQL
  • Problem: #475

Problem statement

Content acquisition decides next year's budget by genre. Using every watch session, compute the total time watched per genre of the title watched, in hours.

Return the columns genre and watch_hours, where watch_hours is the sum of watched_minutes divided by 60 and rounded to 2 decimal places. Genres with no sessions do not appear. Order the rows by watch_hours from highest to lowest, and by genre alphabetically when two genres tie.

Tables

Table: Title

ColumnType
title_idint
title_namevarchar
genrevarchar

Primary key: title_id.

One row per title; every title has exactly one primary genre.

Table: WatchSession

ColumnType
session_idint
profile_idint
title_idint
watched_minutesint

Primary key: session_id.

One row per playback session; title_id always names a row of Title.

Examples

Example 1

Title

title_idtitle_namegenre
1Coastal CopsThriller
2BackbenchersComedy
3Silk Road StoriesDocumentary
4Gully KingsComedy
5Desert RoseRomance

WatchSession

session_idprofile_idtitle_idwatched_minutes
11150
22225
31445
43120
53370
622100

Output

genrewatch_hours
Comedy2.83
Documentary1.17
Thriller1.17

How to solve Watch Hours by Genre

The genre lives on Title and the minutes on WatchSession, so the query joins the two on title_id and then aggregates: GROUP BY t.genre and SUM(w.watched_minutes). An inner join is right here because the statement leaves out genres nobody watched.

Convert to hours after summing — SUM(minutes) / 60 — and round once. Rounding each session's hours first and adding the rounded pieces can drift by a few hundredths from the true total. / is decimal division in MySQL, so 45 minutes become 0.75 hours, not 0.

The order has two keys: the hours descending, then the genre name ascending to settle equal totals. Sorting by the raw minute total gives the same order, since two genres with the same rounded hours have the same minutes here (minutes are whole numbers). The plan is one pass over the sessions plus one primary-key lookup each.

Reference solution (MySQL)

SELECT t.genre, ROUND(SUM(w.watched_minutes) / 60, 2) AS watch_hours
FROM WatchSession w
JOIN Title t ON t.title_id = w.title_id
GROUP BY t.genre
ORDER BY watch_hours DESC, t.genre

Another way

SELECT genre, ROUND(total / 60, 2) AS watch_hours
FROM (SELECT t.genre, SUM(w.watched_minutes) AS total FROM Title t JOIN WatchSession w ON w.title_id = t.title_id GROUP BY t.genre) g
ORDER BY total DESC, genre

← Profiles With No Streams Since the New Year · Accounts With Two or More Kids Profiles →