Point of Entry

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

L4

Asked in

Meta

Problem

Count the purchases of every visitor in event_data whose very first recorded event was a page view, keeping the ones who never bought at zero. Events logged without a user ID all come from one anonymous visitor, judged by the same first-event rule. The list shows whether people who start by just looking around ever turn into buyers, so it runs from the most purchases down.

Everyone starts by looking. Count who came back to buy.

Tables

event_data

Columns: event_id INTEGER, event_type TEXT, user_id INTEGER, event_timestamp TEXT, payload TEXT, tags TEXT, properties TEXT

Expected output

Worked solution and explanation

What this really is

Strip the activity-tracking costume and this is a cohort defined by each user's earliest row, stacked on a count of a different event type that has to keep its zeros. There is a trap in each half. Filter event_type = 'page_view' and you get everyone who ever viewed a page, not everyone whose first event was a view, so users who signed up or clicked first leak into the cohort. Then count purchases through an inner join and every viewer who never bought silently disappears: the look-but-never-buy segment the question exists to measure.

Break down the requirements

Step 1: Define the cohort by first event type

In the first_events CTE, number every event per user by event_timestamp, then keep rn = 1 AND event_type = 'page_view' in viewer_cohort. The earliest event of any type is chosen first and only then checked, which is what 'very first recorded event' means.

Step 2: Count purchase events for the cohort

Join viewer_cohort back to event_data on user_id, with e.event_type = 'purchase' inside the ON clause, and count the matched e.event_id per user. Every purchase counts, whenever it happened.

Step 3: Keep the zero-purchase viewers

Because it is a LEFT JOIN, a viewer with no purchase rows still produces one row with a null e.event_id, and COUNT(e.event_id) scores it 0. COUNT(*) would score that same row 1, and an inner join would drop it entirely.

The solution

WITH first_events AS (
    SELECT user_id, event_type,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_timestamp) AS rn
    FROM event_data
),
viewer_cohort AS (
    SELECT user_id
    FROM first_events
    WHERE rn = 1 AND event_type = 'page_view'
)
SELECT
    vc.user_id,
    COUNT(e.event_id) AS purchase_count
FROM viewer_cohort vc
LEFT JOIN event_data e
    ON vc.user_id = e.user_id AND e.event_type = 'purchase'
GROUP BY vc.user_id
ORDER BY purchase_count DESC

Common follow-up questions

• What if two events share the same timestamp for a user? (`ROW_NUMBER()` is non-deterministic on ties; add `event_id` as a secondary sort key so the first event is reproducible.)
• How would you compute the conversion rate (purchasers over total cohort)? (Count cohort users with `purchase_count` > 0 divided by total cohort size.)
• What if you needed the elapsed time between first view and first purchase? (Join each user's first '`page_view`' timestamp to their first purchase timestamp and take the difference.)
• How would you generalize this to a cohort defined by any first-event type? (Parameterize the `event_type` literal in `viewer_cohort` so any first-event type can define the cohort.)