The Weight of a Click

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

Domain

SQL

Difficulty

hard

Seniority

L5

Asked in

Mercury Insurance

Problem

event_data holds one row per user action, so anyone who repeats an action appears once per repeat. For page_view, search, checkout_start and purchase, report how many unique users performed each event and that count's share of the four counts added together, as a decimal percentage rounded to one place.

Where users actually spend their attention.

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-event distinct-user count dressed up as an engagement report. The skill being probed is whether you count people, not events, and whether you know what the denominator actually is. Anyone can write a GROUP BY. Candidates fall in two places: they reach for COUNT(*), so 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 the shares stop summing to 100 and diverge from the expected output. There is a quieter third trap too: every number in that division is an integer.

Walking through it

Step 1: Scope to the four events

Restrict event_data to 'page_view', 'search', 'checkout_start' and 'purchase' up front with IN. Everything else (logins, crashes, opens) is noise that would otherwise inflate the denominator, and filtering in WHERE shrinks the 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. Rows with a NULL user_id are not people, and COUNT(DISTINCT user_id) already skips them.

Step 3: Divide by the shared total

The denominator is SUM(unique_users) over the four grouped rows, evaluated once as a scalar subquery and not redefined as distinct users across all four events. A user who both searched and purchased lands in two buckets, so this sum is engagement weight, which is exactly why the shares total 100.

Step 4: Convert before dividing, then round

unique_users is an integer, 100 is an integer, and the summed denominator is an integer, so the division truncates. CAST(unique_users AS REAL) makes the whole expression real, and ROUND(..., 1) then delivers the one-decimal figure the statement asks for.

The solution

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 / (SELECT SUM(unique_users) FROM counts), 1) AS pct_of_engaged
FROM counts

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 100 (up to rounding) and read as share of engagement weight, which is the intended metric.

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 the grouping 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 ordering by `event_timestamp`.)