# Behavioral Range > Power users don't just visit more. They do more things. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 · Asked in: Meta ## 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. ## Tables ### users - user_id INTEGER - username TEXT - email TEXT - signup_date TEXT - account_status TEXT - age_bucket TEXT Sample rows (10 of 200): | user_id | username | email | signup_date | account_status | age_bucket | | --- | --- | --- | --- | --- | --- | | 100 | alice | alice@example.com | 2025-02-02 | inactive | 25-34 | | 197 | aaron42 | aaron42@example.com | 2026-03-03 | suspended | 35-44 | | 294 | amelia | amelia@example.com | 2024-04-04 | pending_verification | 45-54 | | 391 | arjun | arjun@example.com | 2025-05-05 | active | 55-64 | | 488 | ava99 | ava99@example.com | 2026-06-06 | inactive | 65+ | | 585 | andrew_k | andrew_k@example.com | 2024-07-07 | suspended | NULL | | 682 | anika | anika@example.com | 2025-08-08 16:34:00 | pending_verification | 18-24 | | 779 | aiden | aiden@example.com | 2026-09-09 14:53:00 | active | 25-34 | | 876 | aria_b | aria_b@example.com | 2024-10-10 | inactive | 35-44 | | 973 | brian | brian@example.com | 2025-11-11 | suspended | 45-54 | ### 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 200 rows: | user_id | event_type_count | | --- | --- | | 100 | 1 | | 197 | 1 | | 294 | 1 | | 682 | 0 | | 1361 | 0 | ## 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/behavioral_range) - [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.