7-Day Onboarding Conversion

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

General Assembly

Problem

What share of the experiments entries made from 2023 onward led to a meaningful session in user_sessions, one with a recorded duration above zero, within 30 days of the entry date? The growth team reads it per variant, alphabetically, as total entries, converted entries and a conversion percentage that keeps its fractional part.

Signed up Monday. Still here by Sunday?

Tables

experiments

Columns: exp_id INTEGER, exp_name TEXT, variant TEXT, user_id INTEGER, outcome REAL, created TEXT, platform TEXT

user_sessions

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

Expected output

Worked solution and explanation

What this really is

This is an existence check per cohort member, dressed up as an experiment readout. The skill being probed is whether you can keep the population fixed while you score its behavior. Anyone can join experiments to user_sessions. The trap is where the 30-day window and the duration test live. Put either one in WHERE and every entry without a qualifying session drops out of the denominator. Since most entries on this data never come back inside their window, every variant then reports a conversion rate near 100%.

Step 1: Pin the cohort before you look at behavior

The population is every experiments row created from 2023 onward, one member per exp_id. A user who entered two experiments is two attempts, each with its own start date, so the grain is the entry, not the user. Fix that set in its own CTE, and nothing downstream is allowed to shrink it.

Step 2: Put the window in the ON clause

The window depends on both sides: the session day minus that entry's entry_date. That makes it a join condition. Inside ON, it only limits which sessions attach, and an entry with no sessions still survives as a single row of NULLs. Moved to WHERE, those NULL rows fail the comparison and disappear.

Step 3: Collapse each entry to one flag

COUNT(CASE WHEN us.session_duration_sec > 0 THEN 1 END) counts only real sessions. The inner CASE yields NULL for a missing duration and for the padding row an unmatched entry carries, and COUNT skips NULLs. The outer CASE turns that count into a clean 0 or 1, however many sessions the entry had.

Step 4: Divide in decimal

SUM(converted) and COUNT(*) are both integers. Integer division turns 3 converters out of 31 into 9 instead of 9.677. Casting the numerator before the division keeps the fraction the readout depends on.

WITH entrants AS (
    SELECT exp_id, variant, user_id, DATE(created) AS entry_date
    FROM experiments
    WHERE created >= '2023-01-01'
),
entry_outcomes AS (
    SELECT
        en.exp_id,
        en.variant,
        CASE
            WHEN COUNT(CASE WHEN us.session_duration_sec > 0 THEN 1 END) > 0
            THEN 1 ELSE 0
        END AS converted
    FROM entrants en
    LEFT JOIN user_sessions us
        ON us.user_id = en.user_id
       AND julianday(DATE(us.session_start)) - julianday(en.entry_date) BETWEEN 0 AND 30
    GROUP BY en.exp_id, en.variant
)
SELECT
    variant,
    COUNT(*) AS total_entries,
    SUM(converted) AS converted_entries,
    CAST(SUM(converted) AS REAL) * 100 / COUNT(*) AS conversion_rate
FROM entry_outcomes
GROUP BY variant
ORDER BY variant

Window in WHERE

The LEFT JOIN pads non-converters with NULL session_start, then WHERE throws those rows away. total_entries collapses to the converters, and every rate reads 100.

Window in ON

Unmatched entries keep their NULL row through the join. The inner CASE scores them 0, COUNT(*) still counts them, and the denominator is the whole cohort.

Common follow-up questions

• Should a user who entered two experiments count once or twice in `total_entries`? (Tests whether the candidate can defend entry grain against user grain, and say how the totals would change under each.)
• If the readout must be per user, which entry anchors the window? (Tests first-entry selection and the effect of overlapping windows.)
• How would you show the variant gap as a lift over 'control' in the same query? (Tests reusing the per-variant totals as a reference value with a scalar subquery or a self-join.)