# Point of Entry

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

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

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.

## Tables

### event_data

- event_id INTEGER
- event_type TEXT
- user_id INTEGER
- event_timestamp TEXT
- payload TEXT
- tags TEXT
- properties TEXT

Sample rows (10 of 200):

| event_id | event_type | user_id | event_timestamp | payload | tags | properties |
| --- | --- | --- | --- | --- | --- | --- |
| 3059 | button_click | 100 | 2026-02-02 01:07:00 | {"button_id": "cancel", "element_class": "btn-primary", "position": {"x": 13, "y": 7}} | ["desktop", "web", "returning"] | {"os": "Edge", "language": "US", "screen_size": "en", "utm_source": "mobile"} |
| 3118 | purchase | 197 | 2026-03-03 02:14:00 | {"amount": 56.93, "currency": "GBP", "items": [{"sku": "SKU1002", "qty": 3}]} | ["ios", "premium", "new_user", "organic"] | {"country": "Windows", "device_type": "FR", "referrer": "fr", "utm_medium": "1920x1080", "browser": "twitter"} |
| 3177 | signup | 294 | 2026-04-04 03:21:00 | {"signup_method": "apple", "referral_code": "REF3X", "accepted_terms": true} | ["android", "free", "campaign_a", "referral", "experiment_v1"] | {"language": "iOS", "screen_size": "es", "utm_source": "desktop", "session_id": "bing", "os": "organic"} |
| 3236 | login | 391 | 2026-05-05 04:28:00 | {"login_method": "password", "remember_me": true, "mfa_used": false} | ["web", "returning"] | {"device_type": "JP", "referrer": "ja", "utm_medium": "1440x900"} |
| 3295 | logout | 488 | 2026-06-06 05:35:00 | {"action": "logout", "value": 50} | ["premium", "new_user", "organic"] | {"screen_size": "de", "utm_source": "tablet", "session_id": "amazon", "os": "Chrome"} |
| 3354 | search | 585 | 2026-07-07 06:42:00 | {"query": "phone", "results_count": 102, "filters": {"category": "electronics"}} | ["free", "campaign_a", "referral", "experiment_v1"] | {"referrer": "mobile", "utm_medium": "375x812", "browser": "email", "country": "Edge", "device_type": "US"} |
| 3413 | add_to_cart | NULL | 2026-08-08 07:49:00 | {"product_id": 1007, "quantity": 2, "price": 58.49, "variant": "medium"} | ["returning", "campaign_b", "featured", "experiment_v2", "ios"] | {"utm_source": "fr", "session_id": "twitter", "os": "Safari", "language": "Windows", "screen_size": "FR"} |
| 3472 | open | 779 | 2026-09-09 08:56:00 | {"action": "open", "value": 80} | ["new_user", "organic"] | {"utm_medium": "bing", "browser": "organic", "country": "iOS"} |
| 3531 | crash | 876 | 2026-01-10 09:03:00 | {"action": "crash", "value": 90} | ["campaign_a", "referral", "experiment_v1"] | {"session_id": "direct", "os": "Firefox", "language": "macOS", "screen_size": "JP"} |
| 3590 | error | 973 | 2026-02-11 10:10:00 | {"action": "error", "value": 100} | ["campaign_b", "featured", "experiment_v2", "ios"] | {"browser": "Chrome", "country": "Android", "device_type": "DE", "referrer": "de", "utm_medium": "tablet"} |

## Expected output

First 5 of 6 rows:

| user_id | purchase_count |
| --- | --- |
| 12128 | 5 |
| 12031 | 4 |
| 11934 | 3 |
| 11837 | 2 |
| 11740 | 1 |

## 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.

> **Pick the first row, then check its type**
>
> Number each user's events by `event_timestamp` with `ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_timestamp)` and keep `rn = 1`. Only then test whether that row is a '`page_view`'. Filtering to page views first and taking the earliest survivor answers a different question: the first page view in each user's history.

---

### 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

**Cohort by first event, then a count that keeps zeros**

```sql
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
```

> **The sort is the bill, and the ON clause guards the zeros**
>
> The first CTE is the dominant cost: it sorts 250M rows across 12M user partitions to find rank 1. The cohort then collapses to a fraction of users, and the `LEFT JOIN` scans the event table again but only matches purchase rows for those `user_id` values. The purchase filter sits in the `ON` clause on purpose; moving it to `WHERE` turns the outer join back into an inner one and re-drops the zero-purchase users.

> **The zero-count tell**
>
> The tell of a junior answer is an inner join from the cohort to purchases. It runs, it returns rows, it looks right, and every viewer who never bought is gone. A strong candidate reaches for the outer join first, because the prompt said to keep users at zero, and treats that clause as the heart of the problem rather than a footnote.

> **`MIN(event_timestamp)` finds the moment but not the type**
>
> `MIN(event_timestamp)` per user gives you when the first event happened, not what it was, so you need a self join back on `user_id` and the minimum timestamp to recover `event_type`, which double-matches on timestamp ties. `ROW_NUMBER()` carries the type along for free and gives you one place to add a tiebreaker.

> **The blank user is a real row**
>
> Events with no `user_id` share one `PARTITION BY user_id` bucket, and on this data that bucket's earliest event is a page view. It enters the cohort, matches no purchase because a `NULL` key never equals anything in the `ON` clause, and comes back with a `purchase_count` of 0: the zero row the outer join exists to keep.

---

## 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.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/point_of_entry)
- [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.