Accounts Streaming Past Their Screen Limit

Hard
495.

Accounts Streaming Past Their Screen Limit

Each plan allows a number of simultaneous screens. Password-sharing detection looks at concurrency: at the moment a stream starts, count the account's streams in progress — every stream with started_at ≤ that moment and ended_at after it, the new stream included. A stream that ended exactly when another began does not overlap it.

Return every account whose highest such count is greater than its plan's max_screens, with the columns account_id, plan_name, max_screens and peak_streams. Order by account_id.

Tables

Table: Plan

ColumnType
plan_namevarchar
max_screensint

Primary key: plan_name.

How many devices may stream at the same time on the plan.

Table: Account

ColumnType
account_idint
plan_namevarchar

Primary key: account_id.

Each account's current plan; plan_name always names a row of Plan.

Table: Stream

ColumnType
stream_idint
account_idint
device_idvarchar
started_atdatetime
ended_atdatetime

Primary key: stream_id.

One row per stream; ended_at is always later than started_at.

Example

The rows must come back in the order shown.

Plan

plan_namemax_screens
Mobile1
Standard2
Premium4

Account

account_idplan_name
801Mobile
802Standard
803Standard
804Premium

Stream

stream_idaccount_iddevice_idstarted_atended_at
1801phone-a2024-09-01 20:00:002024-09-01 21:00:00
2801phone-b2024-09-01 21:00:002024-09-01 22:00:00
3802tv-12024-09-01 19:00:002024-09-01 22:00:00
4802phone-12024-09-01 20:00:002024-09-01 20:30:00
5802laptop-12024-09-01 20:15:002024-09-01 21:00:00
6803tv-22024-09-01 19:00:002024-09-01 20:00:00
7803tab-22024-09-01 19:30:002024-09-01 20:30:00
8804tv-32024-09-01 18:00:002024-09-01 23:00:00
9804tv-42024-09-01 18:00:002024-09-01 23:00:00
10801phone-a2024-09-02 08:00:002024-09-02 08:45:00
1 more row

Output

account_idplan_namemax_screenspeak_streams
801Mobile12
802Standard23
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.

Accounts Streaming Past Their Screen Limit — SQL Problem

Each plan allows a number of simultaneous screens. Password-sharing detection looks at concurrency: at the moment a stream starts, count the account's…

  • Difficulty: Hard
  • Topics: Joins, Dates, Aggregation
  • Dialect: MySQL
  • Problem: #495

Problem statement

Each plan allows a number of simultaneous screens. Password-sharing detection looks at concurrency: at the moment a stream starts, count the account's streams in progress — every stream with started_at ≤ that moment and ended_at after it, the new stream included. A stream that ended exactly when another began does not overlap it.

Return every account whose highest such count is greater than its plan's max_screens, with the columns account_id, plan_name, max_screens and peak_streams. Order by account_id.

Tables

Table: Plan

ColumnType
plan_namevarchar
max_screensint

Primary key: plan_name.

How many devices may stream at the same time on the plan.

Table: Account

ColumnType
account_idint
plan_namevarchar

Primary key: account_id.

Each account's current plan; plan_name always names a row of Plan.

Table: Stream

ColumnType
stream_idint
account_idint
device_idvarchar
started_atdatetime
ended_atdatetime

Primary key: stream_id.

One row per stream; ended_at is always later than started_at.

Examples

Example 1

Plan

plan_namemax_screens
Mobile1
Standard2
Premium4

Account

account_idplan_name
801Mobile
802Standard
803Standard
804Premium

Stream

stream_idaccount_iddevice_idstarted_atended_at
1801phone-a2024-09-01 20:00:002024-09-01 21:00:00
2801phone-b2024-09-01 21:00:002024-09-01 22:00:00
3802tv-12024-09-01 19:00:002024-09-01 22:00:00
4802phone-12024-09-01 20:00:002024-09-01 20:30:00
5802laptop-12024-09-01 20:15:002024-09-01 21:00:00
6803tv-22024-09-01 19:00:002024-09-01 20:00:00
7803tab-22024-09-01 19:30:002024-09-01 20:30:00
8804tv-32024-09-01 18:00:002024-09-01 23:00:00
9804tv-42024-09-01 18:00:002024-09-01 23:00:00
10801phone-a2024-09-02 08:00:002024-09-02 08:45:00
11801phone-c2024-09-02 08:30:002024-09-02 09:00:00

Output

account_idplan_namemax_screenspeak_streams
801Mobile12
802Standard23

How to solve Accounts Streaming Past Their Screen Limit

The number of live streams can only rise when a stream starts, so the peak is reached at some start time. For each stream s, a self join finds the streams o of the same account that are live at s.started_at: o.started_at <= s.started_at and o.ended_at > s.started_at. The condition is half-open on purpose — a hand-off at exactly 21:00 (account 801) is not two screens — and s matches itself, so it counts towards its own moment. COUNT(*) per stream is the concurrency at that start; MAX per account is the peak.

Join in the account's plan and keep the accounts whose peak exceeds max_screens in HAVING. Two streams starting at the same second both see each other (both satisfy <=), which is right: account 804's two TVs are two screens at 18:00, within the Premium limit.

The alternative is a sweep line: turn every stream into a +1 event at its start and a −1 at its end, and take a running sum per account. Ordering by time and then by delta puts ends before starts at the same instant, matching the half-open rule. The self join is quadratic per account; the sweep is one sort of 2n events.

Reference solution (MySQL)

WITH concurrency AS (
  SELECT s.account_id, s.stream_id, COUNT(*) AS live
  FROM Stream s
  JOIN Stream o
    ON o.account_id = s.account_id
   AND o.started_at <= s.started_at
   AND o.ended_at > s.started_at
  GROUP BY s.account_id, s.stream_id
)
SELECT a.account_id, a.plan_name, p.max_screens, MAX(c.live) AS peak_streams
FROM concurrency c
JOIN Account a ON a.account_id = c.account_id
JOIN Plan p ON p.plan_name = a.plan_name
GROUP BY a.account_id, a.plan_name, p.max_screens
HAVING MAX(c.live) > p.max_screens
ORDER BY a.account_id

Another way

WITH events AS (
  SELECT account_id, started_at AS t, 1 AS delta FROM Stream
  UNION ALL
  SELECT account_id, ended_at AS t, -1 AS delta FROM Stream
), running AS (
  SELECT account_id, SUM(delta) OVER (PARTITION BY account_id ORDER BY t, delta ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS live
  FROM events
), peaks AS (SELECT account_id, MAX(live) AS peak FROM running GROUP BY account_id)
SELECT a.account_id, a.plan_name, p.max_screens, k.peak AS peak_streams
FROM peaks k JOIN Account a ON a.account_id = k.account_id JOIN Plan p ON p.plan_name = a.plan_name
WHERE k.peak > p.max_screens
ORDER BY a.account_id

← Median Session Length by Device · Free-Trial Conversion and Second-Month Funnel →