The Shape of a User

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

L6

Asked in

Lyft

Problem

Produce the product analytics team's per-user funnel snapshot from event_data: one row per user, with separate columns counting that user's search, add_to_cart, and purchase events.

Every user leaves a trail of events. The report needs them lined up side by side.

Tables

event_data

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

Expected output

Worked solution and explanation

What this really is

This is a long-to-wide reshape wearing an analytics-report costume. The skill under test: can you collapse many event rows per user into one row with a fixed set of typed columns, using conditional aggregation, because standard SQL has no runtime PIVOT that invents column names for you. Anyone can write GROUP BY user_id. The move that separates people is putting the event_type test INSIDE the aggregate, not in a WHERE clause. Filter the type in WHERE and you keep only one kind of event per pass and throw the rest away: a user who searched and bought loses one of their two columns, and the whole point of the report is gone.

Walk the requirements

Step 1: Fold events down to one row per user

GROUP BY user_id gives exactly one output row per user. The null user_id (an unattributed event) folds into its own group and is kept, which is why a row with a blank user appears in the result.

Step 2: Count each type conditionally, in one pass

For each type you want as a column, write SUM(CASE WHEN event_type = 'search' THEN 1 ELSE 0 END) AS searches, and repeat for add_to_cart and purchase. The CASE evaluates per row inside the group, so all three counts come out of a single pass over the user's events.

Filter the type in WHERE

WHERE event_type = 'purchase' scopes the WHOLE query to purchases. You can only produce one type's column per run, and the same user's searches and add_to_cart events are discarded before the GROUP BY sees them.

Test the type inside the aggregate

SUM(CASE WHEN event_type = 'purchase' ...) keeps every row in scope and decides per column what to count. One scan yields all three columns, and every user appears once with the right split.

The solution

SELECT
    user_id,
    SUM(CASE WHEN event_type = 'search' THEN 1 ELSE 0 END) AS searches,
    SUM(CASE WHEN event_type = 'add_to_cart' THEN 1 ELSE 0 END) AS add_to_carts,
    SUM(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END) AS purchases
FROM event_data
GROUP BY user_id

Common follow-up questions

• The team now wants `add_to_cart`-to-purchase conversion per user as a fourth column. How do you add it, and what breaks if a brand new event type appears next week? (Tests whether the candidate can extend the reshape without rewriting it, and whether they recognize that new column names cannot be produced dynamically in standard SQL.)
• With 12,000,000 distinct users, where does the time go in this query, and how would a materialized per-user rollup change the plan? (Probes understanding of how `user_id` cardinality drives the group-and-sort cost and where an index or rollup actually helps.)
• Events like 'login' and 'crash' never appear in any column. Is that intended, and how would you surface a catch-all 'other' count if asked? (Tests awareness that the `CASE` branches are not exhaustive and that the reshape silently ignores unlisted types.)