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
| Column | Type |
|---|---|
| start_id | int |
| account_id | int |
| city | varchar |
| started_at | datetime |
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_id | account_id | city | started_at |
|---|---|---|---|
| 1 | 71 | Mumbai | 2025-04-05 09:15:00 |
| 2 | 71 | Pune | 2025-04-05 13:40:00 |
| 3 | 71 | Delhi | 2025-04-05 22:05:00 |
| 4 | 71 | Mumbai | 2025-04-06 00:10:00 |
| 5 | 72 | Chennai | 2025-04-05 19:00:00 |
| 6 | 72 | NULL | 2025-04-05 19:30:00 |
| 7 | 72 | Chennai | 2025-04-05 20:00:00 |
| 8 | 72 | Kolkata | 2025-04-05 21:00:00 |
| 9 | 73 | Lucknow | 2025-04-06 18:00:00 |
| 10 | 73 | Indore | 2025-04-06 19:00:00 |
| 11 | 73 | Bengaluru | 2025-04-06 23:59:00 |
Output
| account_id | stream_date | cities |
|---|---|---|
| 71 | 2025-04-05 | 3 |
| 73 | 2025-04-06 | 3 |
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) >= 3Another 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 →