Top Identified Event Types

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

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.

The top users by events, but only the identifiable ones.

Tables

event_data

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

Expected output

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.

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.

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