Hotstar: Final-Day Paid Sign-Ups by Hour With a Running Total
On the day of a league final, Hotstar's growth team watches paid sign-ups hour by hour to see when the paywall converts.
- Difficulty: Medium
- Topics: Window Functions, Dates, Aggregation
- Dialect: MySQL
- Problem: #715
Problem statement
On the day of a league final, Hotstar's growth team watches paid sign-ups hour by hour to see when the paywall converts. The final in this data is on 2025-05-25; free sign-ups (plan free) are not paid and are left out, as are sign-ups on other days.
Return signup_hour (0–23), paid_signups in that hour, and running_total (paid sign-ups from the start of the day through that hour) for every hour of 2025-05-25 with at least one paid sign-up, ordered by signup_hour.
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: SignUp
| Column | Type |
|---|---|
| signup_id | int |
| user_id | int |
| plan | enum(free, mobile, super, premium) |
| signed_up_at | datetime |
Primary key: signup_id.
One row per new account, with the plan chosen at sign-up. Times are IST.
Examples
Example 1
SignUp
| signup_id | user_id | plan | signed_up_at |
|---|---|---|---|
| 1 | 901 | mobile | 2025-05-24 23:58:00 |
| 2 | 902 | super | 2025-05-25 00:03:00 |
| 3 | 903 | free | 2025-05-25 18:10:00 |
| 4 | 904 | mobile | 2025-05-25 18:45:00 |
| 5 | 905 | premium | 2025-05-25 18:59:59 |
| 6 | 906 | mobile | 2025-05-25 19:00:00 |
| 7 | 907 | super | 2025-05-25 21:30:00 |
| 8 | 908 | mobile | 2025-05-26 00:00:00 |
Output
| signup_hour | paid_signups | running_total |
|---|---|---|
| 0 | 1 | 1 |
| 18 | 2 | 3 |
| 19 | 1 | 4 |
| 21 | 1 | 5 |
How to solve Hotstar: Final-Day Paid Sign-Ups by Hour With a Running Total
Three layers. The filter keeps paid plans and the final's date. With datetimes, the safe way to say "on 2025-05-25" is the half-open range >= '2025-05-25' AND < '2025-05-26': it keeps 00:03 and 23:59 and excludes the stroke of midnight on the 26th; DATE(signed_up_at) = '2025-05-25' (the alternative) is equivalent but cannot use an index on the column.
Next, HOUR(signed_up_at) buckets the rows and COUNT(*) gives each hour's sign-ups — 18:59:59 is still hour 18, 19:00 is hour 19. Hours without paid sign-ups simply do not appear, as the statement allows.
Finally the running total is a window over the aggregated rows: SUM(paid_signups) OVER (ORDER BY signup_hour) adds every hour up to the current one (hours are unique, so the default range frame is the same as a rows frame). The window must sit outside the GROUP BY, in a derived table, because it sums group results. The alternative computes the same cumulative sum with a correlated subquery over the hourly CTE — quadratic in 24 rows, which is nothing.
Reference solution (MySQL)
SELECT signup_hour, paid_signups,
SUM(paid_signups) OVER (ORDER BY signup_hour) AS running_total
FROM (
SELECT HOUR(signed_up_at) AS signup_hour, COUNT(*) AS paid_signups
FROM SignUp
WHERE plan <> 'free'
AND signed_up_at >= '2025-05-25' AND signed_up_at < '2025-05-26'
GROUP BY HOUR(signed_up_at)
) h
ORDER BY signup_hourAnother way
WITH h AS (
SELECT HOUR(signed_up_at) AS signup_hour, COUNT(*) AS paid_signups
FROM SignUp
WHERE plan IN ('mobile', 'super', 'premium') AND DATE(signed_up_at) = '2025-05-25'
GROUP BY HOUR(signed_up_at)
)
SELECT a.signup_hour, a.paid_signups,
(SELECT SUM(b.paid_signups) FROM h b WHERE b.signup_hour <= a.signup_hour) AS running_total
FROM h a
ORDER BY a.signup_hour← Hotstar: Top Two Shows per Language by Unique Viewers · Hotstar: Peak of Three-Sample Smoothed Concurrency per Match →