Active User Penetration Rate

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

hard

Seniority

L5

Asked in

Spotify

Problem

The product analytics team is measuring how deeply each device type has taken hold with its users. A user counts as active on a device type only when their most recent session on it lands within 60 days of the latest session recorded anywhere in the data, their combined session time on it reaches 5,000 seconds, and they have viewed at least 115 pages on it in total. For every device type, report the share of its users who clear all three bars at once, keep the device types where nobody qualifies so they appear with a zero rate, and list them from the deepest penetration to the shallowest, settling any tie on that rate by putting the device types in alphabetical order.

How much of the user base is actually alive?

Tables

user_sessions

Columns: session_id INTEGER, user_id INTEGER, device_id INTEGER, session_start TEXT, session_duration_sec INTEGER, pages_viewed INTEGER

devices

Columns: device_id INTEGER, device_type TEXT, os_name TEXT, os_version TEXT, browser TEXT

Expected output

Worked solution and explanation

Why this problem exists in real interviews

This is a filtered cohort divided by a full population wearing a product-metrics costume. The skill being probed is whether you anchor the denominator before you ever touch the active filter. Penetration rate is active users over all users per device type, and if you build it numerator-first you join the wrong direction and silently drop every device type that has zero active users. On this data smart_tv has no user who clears all three bars, so the wrong join makes it disappear instead of showing 0.0. The interviewer wants to hear 'denominator first, numerator joined in' out loud before you write a line. The second, quieter trap is the recency clock: 'within 60 days' has to count from a fixed point in the data, not from wall-clock now, or the same query returns a different cohort every time it runs.

Break down the requirements

Step 1: Per-user-per-device aggregates

Join user_sessions to devices on device_id, then GROUP BY user_id, device_type. Emit MAX(session_start), SUM(session_duration_sec) and SUM(pages_viewed). One row per (user, device_type), summed across every physical device_id of that type. This is where you collapse a user's many sessions into the three numbers the active bars are tested against.

Step 2: Pin the clock, then apply the three bars

Compute one reference timestamp, MAX(session_start) over the whole table, and measure recency against it instead of julianday('now'). From the per-user aggregates, keep only rows where the latest session is within 60 days of that reference AND total duration is at least 5,000 seconds AND total pages viewed is at least 115, all together. Then COUNT(*) GROUP BY device_type is the numerator (one row per user already, so a plain count is the active-user count). The conjunction matters: a single AND flipped to OR inflates every cohort.

Step 3: Total users per device

A separate CTE over the same join: COUNT(DISTINCT user_id) GROUP BY device_type on the unfiltered population. This is the denominator and the spine of the final result, so it must list every device type, including ones with no actives. DISTINCT is what keeps a user with many sessions, or several device_ids of one type, from being counted more than once.

Step 4: LEFT JOIN total to active

Put total_users on the left, active_users on the right. Wrap the numerator in COALESCE(active_count, 0) and CAST to REAL before dividing, so device types with zero actives stay in the output at a clean 0.0 instead of vanishing or returning integer-truncated zero. Order from the deepest penetration to the shallowest, with device_type as a stable tiebreaker.

The solution

WITH as_of AS (
  SELECT MAX(session_start) AS reference_ts FROM user_sessions
),
user_stats AS (
  SELECT
    us.user_id,
    d.device_type,
    MAX(us.session_start) AS last_session,
    SUM(us.session_duration_sec) AS total_duration,
    SUM(us.pages_viewed) AS total_pages
  FROM user_sessions us
  INNER JOIN devices d ON us.device_id = d.device_id
  GROUP BY us.user_id, d.device_type
),
active_users AS (
  SELECT
    s.device_type,
    COUNT(*) AS active_count
  FROM user_stats s
  CROSS JOIN as_of a
  WHERE julianday(a.reference_ts) - julianday(s.last_session) <= 60
    AND s.total_duration >= 5000
    AND s.total_pages >= 115
  GROUP BY s.device_type
),
total_users AS (
  SELECT
    d.device_type,
    COUNT(DISTINCT us.user_id) AS total_count
  FROM user_sessions us
  INNER JOIN devices d ON us.device_id = d.device_id
  GROUP BY d.device_type
)
SELECT
  tu.device_type,
  CAST(COALESCE(au.active_count, 0) AS REAL) / tu.total_count AS penetration_rate
FROM total_users tu
LEFT JOIN active_users au ON tu.device_type = au.device_type
ORDER BY penetration_rate DESC, tu.device_type

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• Why anchor the 60-day window to MAX(`session_start`) instead of the current time, and when might a passed-in as-of date be better still? (Probes whether you understand that a data-derived reference makes backfills and replays deterministic, unlike wall-clock now.)
• How would you rewrite this against a daily `user_device_stats` rollup? (Tests whether you recognize the 120M-row scan as a rollup candidate and can name the grain.)
• What changes if a user appears under multiple `device_id` values mapping to the same `device_type`? (Checks your grasp of COUNT(DISTINCT `user_id`) semantics versus session-level counts.)
• How would you break out penetration rate by both `device_type` and `os_name`? (Forces you to think about cardinality and whether the denominator changes per slice.)