# The First Door

> The first interaction matters most. Or does it?

Canonical URL: <https://datadriven.io/problems/the_first_door>

Domain: SQL · Difficulty: medium · Seniority: L3

## Problem

Marketing credits each user's acquisition to their first-touch channel: whatever they did first. For each user, return the user_id and that first event's `event_type`, labeled `first_channel`.

## Worked solution and explanation

### What this problem really is

This is an argmin dressed up as marketing attribution: for every user you want the whole row with the smallest `event_timestamp`, then you keep one column off that row. Anyone can find each user's earliest time with a MIN. The trick is carrying the `event_type` that sits on that exact row without splitting users or double-counting ties. Reach for GROUP BY and you get the minimum timestamp but lose the channel that rode along with it; join it back naively and any user with two events at the same instant comes out twice.

---

### The trap: MIN keeps the time, loses the row

**GROUP BY user_id, MIN(event_timestamp)**

Hands back user_id and the earliest timestamp, but not the event_type on that row. To recover the channel you join back on (user_id, event_timestamp), and every tie at that timestamp fans one user out into duplicate rows.

**ROW_NUMBER() ... WHERE rn = 1**

Ranks each user's events in one pass and returns the entire first row, channel included. A second sort key makes the tie deterministic, so exactly one row survives per user.

### Build it step by step

#### Step 1: Partition by user_id, order by time ascending

PARTITION BY user_id keeps each user's events in their own bucket, and ORDER BY event_timestamp ASC puts the earliest first inside each bucket. Drop the partition and you rank the entire table as one stream, so every user but the global earliest disappears.

#### Step 2: Add a deterministic tiebreaker

Two events can share the exact same earliest timestamp. Without a second ORDER BY key the winner is arbitrary and the result stops being reproducible run to run; event_type ASC breaks the tie the same way every time.

#### Step 3: Keep rank 1

Filter the numbered rows to rn = 1 and project user_id plus event_type aliased as first_channel. That is the full first-touch row, not just its timestamp.

---

### The solution

**Row-number for first-touch attribution**

```sql
WITH ranked AS (
    SELECT user_id,
           event_type,
           ROW_NUMBER() OVER (
               PARTITION BY user_id
               ORDER BY event_timestamp ASC, event_type ASC
           ) AS rn
    FROM event_data
)
SELECT user_id,
       event_type AS first_channel
FROM ranked
WHERE rn = 1
ORDER BY user_id;
```

> **Common pitfall**
>
> GROUP BY user_id with MIN(event_timestamp) answers 'when' but not 'which channel'. The moment you join back to fetch event_type, tied timestamps fan a user out into multiple rows.

> **Interviewers watch for**
>
> The tell is whether you pick ROW_NUMBER (exactly one row per user) over RANK or DENSE_RANK, which return every tied row. First-touch wants one channel, so ROW_NUMBER plus a tiebreaker is the senior answer.

> **Performance insight**
>
> At 300M rows the cost is the sort behind the window. An index on (user_id, event_timestamp) lets the engine walk each user's events already ordered, turning a full sort into a range scan.

---

## Common follow-up questions

- If two events tie on both event_timestamp and event_type, how would you make the choice deterministic, and what column would you add to the ORDER BY? _(Tests whether the candidate can make the result fully reproducible when the chosen keys still collide.)_
- How does this query change if the team wants last-touch attribution instead of first-touch? _(Tests understanding that last-touch is the mirror image and only the sort direction changes.)_
- event_type has only ~25 distinct values while user_id has ~15M. Why does user_id come first in the (user_id, event_timestamp) index? _(Tests composite-index design and column-order selectivity for high- vs low-cardinality columns.)_
- Could you get the same result with a correlated subquery finding MIN(event_timestamp) per user instead of a window function, and what would that cost at 300M rows? _(Tests awareness of the correlated-subquery alternative and its cost at scale.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the_first_door)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.