Sign-Up Channels by First-Week Activation — SQL Problem
Growth compares acquisition channels by how quickly new accounts start watching.
- Difficulty: Medium
- Topics: Dates, Joins, Aggregation
- Dialect: MySQL
- Problem: #489
Problem statement
Growth compares acquisition channels by how quickly new accounts start watching. An account is activated when its first watch session started within 7 days of sign-up — at most 7 calendar days after signed_up_on (the sign-up day itself counts as day 0). Accounts that never streamed are not activated.
Return one row per signup_channel with the columns signup_channel, signups and activated_pct (activated accounts as a percentage of signups, rounded to 2 decimal places). Order by activated_pct from highest to lowest, then by signup_channel.
Tables
Table: Account
| Column | Type |
|---|---|
| account_id | int |
| signed_up_on | date |
| signup_channel | enum(organic, referral, telecom_bundle, paid_social) |
Primary key: account_id.
One row per account; telecom_bundle accounts came free with a mobile recharge pack.
Table: WatchSession
| Column | Type |
|---|---|
| session_id | int |
| account_id | int |
| started_at | datetime |
Primary key: session_id.
One row per playback session; sessions never start before the account's sign-up day.
Examples
Example 1
Account
| account_id | signed_up_on | signup_channel |
|---|---|---|
| 1 | 2024-03-01 | organic |
| 2 | 2024-03-01 | telecom_bundle |
| 3 | 2024-03-05 | telecom_bundle |
| 4 | 2024-03-07 | referral |
| 5 | 2024-03-09 | organic |
| 6 | 2024-03-10 | telecom_bundle |
WatchSession
| session_id | account_id | started_at |
|---|---|---|
| 1 | 1 | 2024-03-01 21:00:00 |
| 2 | 2 | 2024-03-08 23:59:00 |
| 3 | 2 | 2024-03-02 10:00:00 |
| 4 | 3 | 2024-03-13 08:00:00 |
| 5 | 4 | 2024-03-20 19:30:00 |
| 6 | 5 | 2024-03-16 07:15:00 |
Output
| signup_channel | signups | activated_pct |
|---|---|---|
| organic | 2 | 100 |
| telecom_bundle | 3 | 33.33 |
| referral | 1 | 0 |
How to solve Sign-Up Channels by First-Week Activation
First reduce sessions to one fact per account: the first session, MIN(started_at). A LEFT JOIN keeps accounts with no session at all (their minimum is NULL), which matters because they still count as sign-ups in the denominator.
An account is activated when DATEDIFF(first_stream, signed_up_on) <= 7. DATEDIFF compares calendar dates, ignoring the time, so day 0 is the sign-up day and a session late on day 7 still counts while one on day 8 does not. For an account that never streamed the difference is NULL, the CASE condition is not true, and the account falls into ELSE 0.
Then group by channel: COUNT(*) sign-ups and 100 * activated / signups, rounded. The EXISTS alternative skips the minimum entirely — any session before midnight at the start of day 8 means the first one was early enough. Both are linear in sessions with an index on account_id.
Reference solution (MySQL)
WITH firsts AS (
SELECT a.account_id, a.signup_channel, a.signed_up_on, MIN(w.started_at) AS first_stream
FROM Account a
LEFT JOIN WatchSession w ON w.account_id = a.account_id
GROUP BY a.account_id, a.signup_channel, a.signed_up_on
)
SELECT signup_channel, COUNT(*) AS signups,
ROUND(100 * SUM(CASE WHEN DATEDIFF(first_stream, signed_up_on) <= 7 THEN 1 ELSE 0 END) / COUNT(*), 2) AS activated_pct
FROM firsts
GROUP BY signup_channel
ORDER BY activated_pct DESC, signup_channelAnother way
SELECT a.signup_channel, COUNT(*) AS signups,
ROUND(100 * SUM(CASE WHEN EXISTS (SELECT 1 FROM WatchSession w WHERE w.account_id = a.account_id AND w.started_at < DATE_ADD(a.signed_up_on, INTERVAL 8 DAY)) THEN 1 ELSE 0 END) / COUNT(*), 2) AS activated_pct
FROM Account a GROUP BY a.signup_channel
ORDER BY activated_pct DESC, a.signup_channel← Playlist Length as Clock Time · Monthly Subscription Revenue With a Running Total →