# The Weight of a Click > Where users actually spend their attention. Canonical URL: Domain: SQL · Difficulty: hard · Seniority: L5 ## Problem The growth team wants an engagement breakdown across four commerce events: page_view, search, checkout_start, and purchase. For each event, show how many unique users performed it and that count as a percentage of the four counts combined, most users first. ## Worked solution and explanation ### What this really is This is a per-event distinct-user count wearing an engagement-report costume. The skill being probed is whether you count PEOPLE, not events, and whether you understand what the denominator actually is. Anyone can write a GROUP BY. The two places candidates fall: they reach for COUNT(*) and a user who searched ten times counts as ten, and they assume the denominator is the number of distinct engaged users when it is really the sum of the four per-event counts. Get the second one wrong and your percentages stop summing to 100 and diverge from the expected output. > **Trick to solving** > > Count distinct users once per event in a single grouped pass, then divide each count by the SUM of those four counts, taken as one scalar subquery over the grouped result. > > 1. Filter to the four commerce events first so nothing else dilutes the totals. > 2. COUNT(DISTINCT user_id) per event_type. > 3. Reference SUM(unique_users) from the same CTE as the shared denominator. --- ### Walking through it #### Step 1: Scope to the four events Restrict event_data to page_view, search, checkout_start, and purchase up front. Everything else (logins, crashes, opens) is noise that would otherwise inflate the denominator. Doing this in the WHERE clause also shrinks the working set the engine has to dedupe. #### Step 2: Count people, not events COUNT(DISTINCT user_id) per event_type collapses a user's repeated events to a single head. This is the line that turns an event log into a people metric. A null user_id is not a person, and COUNT(DISTINCT user_id) already excludes nulls for free. #### Step 3: Divide by the shared total and order The denominator is SUM(unique_users) over the four grouped rows, evaluated once as a scalar subquery, not recomputed per row and not redefined as the distinct users across all four events. Because a user who both searched and purchased lands in two buckets, this sum is engagement weight, which is exactly why the shares total 100. Order by unique_users descending and break ties alphabetically so checkout_start precedes search at the same count. ### The solution **Grouped distinct-user counts with a shared scalar denominator** ```sql WITH counts AS ( SELECT event_type, COUNT(DISTINCT user_id) AS unique_users FROM event_data WHERE event_type IN ('page_view','search','checkout_start','purchase') GROUP BY event_type ) SELECT event_type, unique_users, ROUND(CAST(unique_users AS REAL) * 100.0 / (SELECT SUM(unique_users) FROM counts), 1) AS pct_of_engaged FROM counts ORDER BY unique_users DESC, event_type ``` > **Common pitfall** > > COUNT(*) counts events; COUNT(DISTINCT user_id) counts users. A user with ten page_views is one viewer, not ten. This single substitution is the difference between a correct breakdown and inflated vanity numbers, and it is the first thing an interviewer scans your query for. **Distinct users across the four events** Defining the denominator as COUNT(DISTINCT user_id) over all four events counts each engaged person once. The four shares then do NOT sum to 100 because a user in two buckets is counted twice in the numerators but once below. This does not match the expected output. **Sum of the four per-event counts** SUM(unique_users) adds the four bucket counts, so every numerator is part of the denominator. The shares sum to exactly 100 and read as 'share of engagement weight', which is the intended metric. > **Interviewers watch for** > > Watch whether the candidate computes the denominator once. A correlated subquery that re-sums the CTE for every output row, or a self-join to total it, gives the same answer at extra cost. A single scalar subquery over the four-row CTE is effectively free. > **Performance insight** > > At 400M rows the only real work is one grouped pass with four distinct-user counts, ideally satisfied straight from an index on (event_type, user_id). The denominator is a scalar over four rows, so it costs nothing. The WHERE on event_type prunes about 21 of the 25 event types before any deduplication happens. --- ## Common follow-up questions - If we want each event's reach as a percentage of all distinct engaged users instead, how does the query change? _(Tests whether they switch the denominator to a true distinct-headcount subquery and accept that shares no longer sum to 100.)_ - How would you produce this same breakdown per week to track engagement trends? _(Tests date truncation in GROUP BY and per-window denominators.)_ - What if the team wants only users whose first event in the window was a page_view? _(Tests conditional aggregation and adding event_timestamp ordering.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_weight_of_a_click) - [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.