Hotstar: Final-Day Paid Sign-Ups by Hour With a Running Total

Medium
715.

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. 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

ColumnTypeValues
signup_idint
user_idint
planenumfree, mobile, super, premium
signed_up_atdatetime

Primary key: signup_id.

One row per new account, with the plan chosen at sign-up. Times are IST.

Example

The rows must come back in the order shown.

SignUp

signup_iduser_idplansigned_up_at
1901mobile2025-05-24 23:58:00
2902super2025-05-25 00:03:00
3903free2025-05-25 18:10:00
4904mobile2025-05-25 18:45:00
5905premium2025-05-25 18:59:59
6906mobile2025-05-25 19:00:00
7907super2025-05-25 21:30:00
8908mobile2025-05-26 00:00:00

Output

signup_hourpaid_signupsrunning_total
011
1823
1914
2115
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: 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

ColumnType
signup_idint
user_idint
planenum(free, mobile, super, premium)
signed_up_atdatetime

Primary key: signup_id.

One row per new account, with the plan chosen at sign-up. Times are IST.

Examples

Example 1

SignUp

signup_iduser_idplansigned_up_at
1901mobile2025-05-24 23:58:00
2902super2025-05-25 00:03:00
3903free2025-05-25 18:10:00
4904mobile2025-05-25 18:45:00
5905premium2025-05-25 18:59:59
6906mobile2025-05-25 19:00:00
7907super2025-05-25 21:30:00
8908mobile2025-05-26 00:00:00

Output

signup_hourpaid_signupsrunning_total
011
1823
1914
2115

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_hour

Another 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 →