# Behavioral Range

> Power users don't just visit more. They do more things.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

For each user, count their unique event types, including users with zero events. Users with no events should show 0. Return user ID and the event type count.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests LEFT JOIN with `COUNT(DISTINCT ...)` to include users with zero events. Interviewers check whether you know the difference between COUNT(*) and COUNT(DISTINCT column) in the context of outer joins.

---

### Break down the requirements

#### Step 1: Left join users to events

`LEFT JOIN event_data ON users.user_id = event_data.user_id` preserves all 10M users, including those with no events.

#### Step 2: Count distinct event types per user

`COUNT(DISTINCT event_type)` counts unique types. For users with no events, the LEFT JOIN produces NULL for event_type, and COUNT(DISTINCT NULL) returns 0.

---

### The solution

**Left join with distinct count for zero-inclusive metric**

```sql
SELECT u.user_id, COUNT(DISTINCT e.event_type) AS event_type_count
FROM users u
LEFT JOIN event_data e ON u.user_id = e.user_id
GROUP BY u.user_id
```

> **Cost Analysis**
>
> The LEFT JOIN produces up to 200M rows (all events matched to users). The GROUP BY reduces to 10M user-level rows. An index on `event_data(user_id)` is essential for the join.

> **Interviewers Watch For**
>
> Using LEFT JOIN, not INNER JOIN. The prompt says "including users with zero events," which requires preserving unmatched users.

> **Common Pitfall**
>
> Using `COUNT(event_type)` instead of `COUNT(DISTINCT event_type)`. Without DISTINCT, you count total events, not unique event types.

---

## Common follow-up questions

- How would you pre-aggregate events to improve performance? _(Aggregate DISTINCT event types per user in a subquery, then LEFT JOIN from users.)_
- What if you only wanted to count specific event types? _(Add a WHERE or ON clause filter for the target event types.)_
- How would you find users with more than 5 distinct event types? _(Wrap in a HAVING COUNT(DISTINCT event_type) > 5 or filter the outer query.)_

## Related

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