The Conversion Story

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

L5

Asked in

Visa

Problem

Our referral-source conversion numbers don't reconcile, so we're rebuilding them from event_data, labelling each signup or purchase event by its raw tags text as the first of "organic", "referral", "campaign_a" and "campaign_b" that appears there with its double quotes, checked in that order, or 'other' when none does. For every referral_source with at least one signup event, report signup_users, purchase_users and funnel_users (unique users who signed up, purchased, or did either) plus conversion_rate, purchasers over signups rounded to 4 decimal places, with the sources in alphabetical order.

Signups are one thing. Paid purchases are another. Find the gap by source.

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-source people count wearing a tag-parsing costume. Everyone builds the source label; what separates candidates is counting users, not events, at three grains at once: who signed up, who paid, and who reached either step. Tally rows and a user who signed up twice under one source counts twice in the base. Skip the step filter because the conditional counts look self-filtering, and every user who merely browsed lands in funnel_users, so the columns stop reconciling with each other.

Build it in order

Step 1: Derive the source from tags

There is no referral_source column. Test the raw tags text with LIKE in priority order, organic first and other last, so an event carrying two tokens lands in the higher one. Keep the double quotes inside each pattern so campaign_a only matches the whole token, never a longer tag that happens to contain it. A NULL tags matches nothing and falls through to other.

Step 2: Keep only the two funnel steps

Filter to event_type IN ('signup', 'purchase') before counting anything. The signup and purchase columns would survive without it, but the either-step column would not: drop the filter and a user whose only events are page views and searches inflates funnel_users.

Step 3: Count people at three grains

COUNT(DISTINCT CASE WHEN event_type = 'signup' THEN user_id END) is the signup base, the same shape on 'purchase' is the payers, and a plain COUNT(DISTINCT user_id) over the filtered rows is everyone who reached either step. The CASE hands back NULL for the other step, and the count ignores it.

Step 4: Guard the base, then divide

HAVING drops a source with purchases but no signups, since it has nothing to convert from. The outer query divides the two named counts once, multiplying by 1.0 so integer division cannot floor the rate to zero, and rounds to 4 places.

The solution

WITH sourced_events AS (
  SELECT
    user_id,
    event_type,
    CASE
      WHEN tags LIKE '%"organic"%'    THEN 'organic'
      WHEN tags LIKE '%"referral"%'   THEN 'referral'
      WHEN tags LIKE '%"campaign_a"%' THEN 'campaign_a'
      WHEN tags LIKE '%"campaign_b"%' THEN 'campaign_b'
      ELSE 'other'
    END AS referral_source
  FROM event_data
  WHERE event_type IN ('signup', 'purchase')
),
source_funnel AS (
  SELECT
    referral_source,
    COUNT(DISTINCT CASE WHEN event_type = 'signup'   THEN user_id END) AS signup_users,
    COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END) AS purchase_users,
    COUNT(DISTINCT user_id) AS funnel_users
  FROM sourced_events
  GROUP BY referral_source
  HAVING COUNT(CASE WHEN event_type = 'signup' THEN 1 END) > 0
)
SELECT
  referral_source,
  signup_users,
  purchase_users,
  funnel_users,
  ROUND(purchase_users * 1.0 / signup_users, 4) AS conversion_rate
FROM source_funnel
ORDER BY referral_source;

Common follow-up questions

• How would you count only users who signed up and then purchased later under the same source? (Pushes from step counts to a true per-user funnel ordered by `event_timestamp`.)
• An event's `tags` carries both `organic` and `campaign_a`. Which source wins, and how would you let marketing change that priority without editing the query? (Probes the priority order and moving it into a lookup table.)
• What would you do instead of dropping a source that has purchases but no signups? (Tests guarding a zero denominator with `NULLIF` versus excluding the row.)