Sign-Up Channels by First-Week Activation

Medium
489.

Sign-Up Channels by First-Week Activation

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

ColumnTypeValues
account_idint
signed_up_ondate
signup_channelenumorganic, 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

ColumnType
session_idint
account_idint
started_atdatetime

Primary key: session_id.

One row per playback session; sessions never start before the account's sign-up day.

Example

The rows must come back in the order shown.

Account

account_idsigned_up_onsignup_channel
12024-03-01organic
22024-03-01telecom_bundle
32024-03-05telecom_bundle
42024-03-07referral
52024-03-09organic
62024-03-10telecom_bundle

WatchSession

session_idaccount_idstarted_at
112024-03-01 21:00:00
222024-03-08 23:59:00
322024-03-02 10:00:00
432024-03-13 08:00:00
542024-03-20 19:30:00
652024-03-16 07:15:00

Output

signup_channelsignupsactivated_pct
organic2100
telecom_bundle333.33
referral10
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.

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

ColumnType
account_idint
signed_up_ondate
signup_channelenum(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

ColumnType
session_idint
account_idint
started_atdatetime

Primary key: session_id.

One row per playback session; sessions never start before the account's sign-up day.

Examples

Example 1

Account

account_idsigned_up_onsignup_channel
12024-03-01organic
22024-03-01telecom_bundle
32024-03-05telecom_bundle
42024-03-07referral
52024-03-09organic
62024-03-10telecom_bundle

WatchSession

session_idaccount_idstarted_at
112024-03-01 21:00:00
222024-03-08 23:59:00
322024-03-02 10:00:00
432024-03-13 08:00:00
542024-03-20 19:30:00
652024-03-16 07:15:00

Output

signup_channelsignupsactivated_pct
organic2100
telecom_bundle333.33
referral10

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_channel

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