Hotstar: Accounts Streaming From Three Cities in a Day

Medium
711.

Hotstar: Accounts Streaming From Three Cities in a Day

MediumAggregationDates

MySQL

Hotstar's trust and safety team flags possible password sharing: a single account that starts streams from three or more different cities on the same calendar day is unlikely to be one household. The city comes from an IP lookup; when the lookup fails, city is NULL and the stream says nothing about location.

Return account_id, stream_date (the calendar date) and cities (the number of distinct non-NULL cities that day) for every account and day with at least 3 distinct cities. Return the rows in any order.

A practice scenario set at Hotstar: the tables and data are invented, and this is not a question from, or endorsed by, Hotstar.

Tables

Table: StreamStart

ColumnType
start_idint
account_idint
cityvarchar
started_atdatetime

Primary key: start_id.

One row per stream started on an account, with the city resolved from its IP address (NULL if unresolved).

Example

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

StreamStart

start_idaccount_idcitystarted_at
171Mumbai2025-04-05 09:15:00
271Pune2025-04-05 13:40:00
371Delhi2025-04-05 22:05:00
471Mumbai2025-04-06 00:10:00
572Chennai2025-04-05 19:00:00
672NULL2025-04-05 19:30:00
772Chennai2025-04-05 20:00:00
872Kolkata2025-04-05 21:00:00
973Lucknow2025-04-06 18:00:00
1073Indore2025-04-06 19:00:00
1 more row

Output

account_idstream_datecities
712025-04-053
732025-04-063
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.

Hotstar: Accounts Streaming From Three Cities in a Day

Hotstar's trust and safety team flags possible password sharing: a single account that starts streams from three or more different cities on the same…

  • Difficulty: Medium
  • Topics: Aggregation, Dates
  • Dialect: MySQL
  • Problem: #711

Problem statement

Hotstar's trust and safety team flags possible password sharing: a single account that starts streams from three or more different cities on the same calendar day is unlikely to be one household. The city comes from an IP lookup; when the lookup fails, city is NULL and the stream says nothing about location.

Return account_id, stream_date (the calendar date) and cities (the number of distinct non-NULL cities that day) for every account and day with at least 3 distinct cities. Return the rows in any order.

A practice scenario set at Hotstar: the tables and data are invented, and this is not a question from, or endorsed by, Hotstar.

Tables

Table: StreamStart

ColumnType
start_idint
account_idint
cityvarchar
started_atdatetime

Primary key: start_id.

One row per stream started on an account, with the city resolved from its IP address (NULL if unresolved).

Examples

Example 1

StreamStart

start_idaccount_idcitystarted_at
171Mumbai2025-04-05 09:15:00
271Pune2025-04-05 13:40:00
371Delhi2025-04-05 22:05:00
471Mumbai2025-04-06 00:10:00
572Chennai2025-04-05 19:00:00
672NULL2025-04-05 19:30:00
772Chennai2025-04-05 20:00:00
872Kolkata2025-04-05 21:00:00
973Lucknow2025-04-06 18:00:00
1073Indore2025-04-06 19:00:00
1173Bengaluru2025-04-06 23:59:00

Output

account_idstream_datecities
712025-04-053
732025-04-063

How to solve Hotstar: Accounts Streaming From Three Cities in a Day

The unit of the question is an (account, calendar day) pair, so group by the account and by DATE(started_at), which drops the time of day. A stream at 00:10 belongs to the new day: account 71 reaches three cities on 5 April, while its after-midnight Mumbai stream starts a fresh day with just one.

Within each group, COUNT(DISTINCT city) counts different cities — two Chennai streams are one city — and, like every aggregate except COUNT(*), it skips NULLs, so an unresolved lookup neither adds a city nor breaks anything. The threshold is on the aggregate, hence HAVING … >= 3. Account 72 has Chennai, a NULL and Kolkata: two cities, not flagged.

The alternative first builds the distinct (account, day, city) triples — using LEFT(started_at, 10) for the date, which works because the stored format is fixed — after which a plain COUNT(*) per account and day is the number of cities. One scan plus a group-by either way.

Reference solution (MySQL)

SELECT account_id, DATE(started_at) AS stream_date, COUNT(DISTINCT city) AS cities
FROM StreamStart
GROUP BY account_id, DATE(started_at)
HAVING COUNT(DISTINCT city) >= 3

Another way

SELECT account_id, stream_date, COUNT(*) AS cities
FROM (SELECT DISTINCT account_id, LEFT(started_at, 10) AS stream_date, city FROM StreamStart WHERE city IS NOT NULL) d
GROUP BY account_id, stream_date
HAVING COUNT(*) >= 3

← Hotstar: Worst Rebuffering CDN and Device in Each City · Hotstar: Subscriptions With No Play in Their First Week →