The First Door

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

Domain

SQL

Difficulty

medium

Seniority

L3

Asked in

Capital One

Problem

app_event_deliveries records every arrival of an app event, so an event the pipeline redelivered appears again under the same event_uuid and event_ts, and a user's first touch is the event_name of their earliest event. Return each user's user_id exactly once with that name as first_event; deliveries carrying no user_id belong to nobody.

The first interaction matters most. Or does it?

Tables

app_event_deliveries

Columns: delivery_id INTEGER, event_uuid TEXT, user_id INTEGER, event_name TEXT, event_ts TEXT, received_ts TEXT

Expected output

Worked solution and explanation

What this problem really is

This is an argmin over a log that lies about how many things happened. Each user needs the row with the smallest event_ts, and one column off that row. Anyone can find the earliest time with MIN(event_ts). The trap is that the pipeline redelivers events: the same event_uuid lands two or three times with the same event_ts. Join back on the minimum, or keep every row tied at the earliest instant, and a redelivered first event comes out once per copy. That user now has three first touches.

The trap: the earliest instant is not one row

`MIN(event_ts)` then join back

Finds the right instant, then matches every delivery at that instant. A redelivered first event fans one user out into duplicate rows, and every count built downstream inflates.

Number each user's deliveries, keep the first

ROW_NUMBER() over each user's deliveries sorted by time hands out position 1 to exactly one row, so redeliveries collapse without a separate dedup pass.

Build it step by step

Step 1: Drop deliveries with no user_id

Some deliveries arrive without a user_id. A window treats NULL as one more partition, so without WHERE user_id IS NOT NULL a phantom user shows up with its own first event.

Step 2: Order each user's deliveries by event_ts

PARTITION BY user_id keeps each user in their own bucket, and event_ts ASC puts the moment the event happened first. Use the event time, not received_ts: a late-arriving delivery of an early event is still the first touch.

Step 3: Make the winner a single row

Redeliveries tie on event_ts, so add received_ts (the first arrival wins) and then delivery_id, which is unique. Now position 1 belongs to exactly one delivery per user.

Step 4: Keep position 1

Filter to rn = 1 and project user_id with event_name as first_event. One row per user, carrying the name from the earliest event.

The solution

WITH ranked AS (
    SELECT user_id,
           event_name,
           ROW_NUMBER() OVER (
               PARTITION BY user_id
               ORDER BY event_ts ASC, received_ts ASC, delivery_id ASC
           ) AS rn
    FROM app_event_deliveries
    WHERE user_id IS NOT NULL
)
SELECT user_id,
       event_name AS first_event
FROM ranked
WHERE rn = 1;

Common follow-up questions

• How would you count first-touch users per `event_name` without double counting redeliveries? (Tests carrying the one-row-per-user result into an aggregate.)
• What changes if two different `event_uuid` values share a user's earliest `event_ts`? (Tests whether the candidate separates true ties from redeliveries and picks a defensible rule.)
• How does this change for last-touch attribution? (Tests that only the sort direction moves, and that the dedup concern still applies.)