# Top Identified Event Types > The top users by events, but only the identifiable ones. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 · Asked in: Meta ## Problem Find the three event types with the most events in `event_data`, keeping only the types where events carrying a `user_id` outnumber anonymous ones. The growth analytics team reads that list busiest first, and when types tie for a spot, the one earliest in the alphabet takes it. ## 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 3 of 3 rows: | event_type | total_events | | --- | --- | | purchase | 29 | | button_click | 14 | | crash | 13 | ## Worked solution and explanation ### What this really is This is a per-group qualification test dressed up as a popularity chart. Counting events per type is the easy half. What separates candidates is comparing two sub-counts of the **same group** (rows with a `user_id` against rows without one) and filtering on that comparison after grouping, then cutting three rows where the cut lands in a tie. Skip the comparison and `page_view`, with 17 events of which 12 are anonymous, takes second place. Skip the tie rule and the third row is any of seven types sitting at 13. ### Building it #### Step 1: Count every event per `event_type` Group `event_data` by `event_type` and take `COUNT(*)`. Every row counts toward the total, anonymous ones included: the ask is the busiest types, not the busiest logged-in types, so nothing gets filtered out before the count. #### Step 2: Qualify the type, not the rows Identified versus anonymous is a property of the whole type, so the test runs after grouping. Two conditional sums inside `HAVING`, one counting rows where `user_id IS NOT NULL` and one where `user_id IS NULL`, compare the two halves of the same group. The comparison is strict, so an even split does not qualify. #### Step 3: Sort, break the tie, then cut Sort by `total_events` descending. Here `purchase` (29) and `button_click` (14) are clear, then seven types tie at 13. `event_type ASC` is the statement's alphabetical rule and decides that `crash` takes the third slot; only after that does `LIMIT 3` cut. **Conditional counts qualify each type; the tie key picks the third** ```sql SELECT event_type, COUNT(*) AS total_events FROM event_data GROUP BY event_type HAVING SUM(CASE WHEN user_id IS NOT NULL THEN 1 ELSE 0 END) > SUM(CASE WHEN user_id IS NULL THEN 1 ELSE 0 END) ORDER BY total_events DESC, event_type ASC LIMIT 3 ``` **Filter rows first** `WHERE user_id IS NOT NULL` throws anonymous rows away before counting. The anonymous half of the comparison no longer exists, and `total_events` becomes an identified-only count. It matches here only because `purchase`, `button_click` and `crash` have no anonymous events; next week's data will not be so kind. **Compare inside the group** Keep every row, count both halves with conditional sums, and let `HAVING` drop the type. `COUNT(*)` stays the true total, and `page_view` is excluded because 12 of its 17 events are anonymous. > **A tie at the cut is a coin flip** > > Seven types (`crash`, `error`, `login`, `logout`, `open`, `search`, `signup`) all have 13 events. `ORDER BY total_events DESC LIMIT 3` alone lets the engine hand back any of them in third place, and the choice can change between runs or engines. The key you add before `LIMIT` is the tie rule, so it has to be the one the business stated. > **Name where the condition lives before you type it** > > The seniority tell is asking out loud whether a condition is about a row or about a group. 'Identified outnumber anonymous' is about the type, so it lives in `HAVING`. Reaching for `WHERE user_id IS NOT NULL` first shows pattern-matching on the word non-null. > **One pass, three counters per type** > > `COUNT(*)` and both conditional sums ride the same single scan of `event_data`; the aggregate state is three counters for each of 14 event types. At 200 million rows the cost is the scan itself, and the sort and `LIMIT` touch a handful of rows. ## Common follow-up questions - How would you return the identified share next to `total_events`? _(Tests reusing the identified conditional sum as a numerator over `COUNT(*)` without integer division.)_ - What changes if the team wants the top three per month of `event_timestamp`? _(Tests moving from one global cut to a top three scoped to each month.)_ - If an `event_type` has no anonymous events at all, does it qualify? _(Yes: an anonymous count of 0 loses to any identified event, which is why the `ELSE 0` matters.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/top_identified_event_types) - [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.