Behavioral Range

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

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.

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

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

users: 10 of 200 rows
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

Columns: event_id INTEGER, event_type TEXT, user_id INTEGER, event_timestamp TEXT, payload TEXT, tags TEXT, properties TEXT

event_data: 10 of 200 rows
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

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

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