# The Shape of a User > Every user leaves a trail of events. The report needs them lined up side by side. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L6 ## Problem Our product analytics team wants a per-user funnel snapshot pulled from the raw event log. Produce one row per user, with separate columns counting that user's search, add_to_cart, and purchase events. ## 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 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. > **Trick to solving** > > The phrase "separate columns for each type" is the reshape signal. With no PIVOT keyword, you fake it with one conditional SUM per column. > > 1. Spot the reshape language: one row per user, one column per named type. > 2. Write SUM(CASE WHEN event_type = 'X' THEN 1 ELSE 0 END) once per type. > 3. GROUP BY user_id so every row folds into its user. --- ### 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 null row can appear 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 searches and add_to_carts for the same user are already 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 **Conditional-aggregation reshape** ```sql 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 pitfall** > > Drop the ELSE 0 and a user with no purchases gets NULL in that cell instead of 0, so the dashboard renders blanks that quietly break sums and averages downstream. Switching SUM(...) to COUNT(CASE WHEN event_type = 'purchase' THEN 1 END) happens to work only because the THEN branch omits the false case; the SUM-with-1/0 form is the one to reach for because its zero handling is explicit and obvious to a reviewer. > **Interviewers watch for** > > The tell of a senior answer is the type test living inside the aggregate rather than in WHERE, and clean SUM(CASE ... ELSE 0) columns that guarantee a numeric zero for absent types. Candidates who reach for a self-join per event type, or run three separate filtered queries and stitch them, are signalling they have not internalized the one-pass reshape. > **Cost analysis** > > At 250,000,000 rows this is a single grouped scan, which is already the cheap plan: all three columns come from one pass, so there is nothing to gain by splitting per type. If this feeds a dashboard that reruns constantly, a materialized rollup keyed on user_id is the real win; a covering structure on (user_id, event_type) helps the group-and-count but the aggregate itself stays linear. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_shape_of_a_user) - [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.