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

The growth team is checking whether the people who arrive and simply look around ever turn into buyers. For every user whose very first recorded event was a page view, count how many purchases they later made, and keep the ones who never bought with a count of zero. Order the result from the most purchases down, breaking ties by user ID ascending.

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 definition stacked on a conditional count. You carve out a population by each user's FIRST event, then count a DIFFERENT event type for exactly those users. The trap lives in four words of the prompt: 'with a count of zero'. Define the cohort, switch to an inner join to count purchases, and every viewer who never bought silently disappears from your result. The grader is counting those exact people, so an inner join quietly deletes the rows you were asked to keep.

Break down the requirements

Step 1: Define the cohort by first event type

Use a CTE with ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_timestamp) and filter to rn = 1 AND event_type = 'page_view'. This is the earliest event of ANY type, then a check that it was a page view, not 'the first page_view in their history'.

Step 2: Count purchase events for the cohort

Left join the cohort users back to event_data filtered to event_type = 'purchase' and count the matched event rows per user.

Step 3: Keep the zero-purchase viewers

Because it is a LEFT JOIN, a viewer with no purchase rows still produces one output row, and COUNT over the null join partner returns 0 instead of dropping them. Swap in an inner join here and those users vanish.

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, vc.user_id ASC

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 the cohort CTE so any first-event type can define the cohort.)