# Users With Purchase Events

> At least one purchase. That changes everything.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

Return all user IDs for users who have triggered at least one purchase event, by user ID.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests filtering by event type and deduplicating user IDs. Interviewers check whether you can extract a unique set of entities matching a condition and sort them deterministically.

---

### Break down the requirements

#### Step 1: Filter to purchase events

`WHERE event_type = 'purchase'` restricts the 200M row table to purchase events only.

#### Step 2: Return distinct user IDs ordered

`SELECT DISTINCT user_id ORDER BY user_id` produces a deduplicated, sorted list.

---

### The solution

**Filter and deduplicate by event type**

```sql
SELECT DISTINCT user_id
FROM event_data
WHERE event_type = 'purchase'
ORDER BY user_id
```

> **Cost Analysis**
>
> With 20 event types, the filter reduces 200M rows to ~10M. The DISTINCT reduces to a subset of the 10M users. An index on `(event_type, user_id)` enables an efficient index-only scan.

> **Interviewers Watch For**
>
> Whether you involve the users table unnecessarily. The prompt only asks for user IDs from event_data, so a join to users adds cost without benefit.

> **Common Pitfall**
>
> Forgetting DISTINCT. A user with 50 purchase events would appear 50 times without deduplication.

---

## Common follow-up questions

- How would you also return the count of purchases per user? _(Replace DISTINCT with GROUP BY user_id and add COUNT(*).)_
- What if event_type had mixed casing ('Purchase', 'PURCHASE')? _(Use LOWER(event_type) = 'purchase' for case-insensitive matching.)_
- How would you find users who had both purchase and view events? _(Intersect two filtered DISTINCT sets, or use GROUP BY with HAVING.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/users_with_purchase_events)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.