# The Conversion Story > Signups are one thing. Paid purchases are another. Find the gap by source. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L5 · Asked in: Visa ## Problem From the event_data table, calculate each referral source's conversion rate from signup to paid purchase. Derive the referral source from the event's tags: classify a row as 'organic' if its tags contain "organic", else 'referral' if it contains "referral", else 'campaign_a' if it contains "campaign_a", else 'campaign_b' if it contains "campaign_b", else 'other'. For each source, the conversion rate is the number of distinct users with a purchase event divided by the number of distinct users with a signup event. Only include sources that have at least one signup, and walk the sources in alphabetical order. Round the rate to 4 decimal places. ## Tables ### event_data - event_id INTEGER - event_type TEXT - user_id INTEGER - event_timestamp TEXT - payload TEXT - tags TEXT - properties TEXT Sample rows (10 of 200): | event_id | event_type | user_id | event_timestamp | payload | tags | properties | | --- | --- | --- | --- | --- | --- | --- | | 3059 | button_click | 100 | 2026-02-02 01:07:00 | {"button_id": "cancel", "element_class": "btn-primary", "position": {"x": 13, "y": 7}} | ["desktop", "web", "returning"] | {"os": "Edge", "language": "US", "screen_size": "en", "utm_source": "mobile"} | | 3118 | purchase | 197 | 2026-03-03 02:14:00 | {"amount": 56.93, "currency": "GBP", "items": [{"sku": "SKU1002", "qty": 3}]} | ["ios", "premium", "new_user", "organic"] | {"country": "Windows", "device_type": "FR", "referrer": "fr", "utm_medium": "1920x1080", "browser": "twitter"} | | 3177 | signup | 294 | 2026-04-04 03:21:00 | {"signup_method": "apple", "referral_code": "REF3X", "accepted_terms": true} | ["android", "free", "campaign_a", "referral", "experiment_v1"] | {"language": "iOS", "screen_size": "es", "utm_source": "desktop", "session_id": "bing", "os": "organic"} | | 3236 | login | 391 | 2026-05-05 04:28:00 | {"login_method": "password", "remember_me": true, "mfa_used": false} | ["web", "returning"] | {"device_type": "JP", "referrer": "ja", "utm_medium": "1440x900"} | | 3295 | logout | 488 | 2026-06-06 05:35:00 | {"action": "logout", "value": 50} | ["premium", "new_user", "organic"] | {"screen_size": "de", "utm_source": "tablet", "session_id": "amazon", "os": "Chrome"} | | 3354 | search | 585 | 2026-07-07 06:42:00 | {"query": "phone", "results_count": 102, "filters": {"category": "electronics"}} | ["free", "campaign_a", "referral", "experiment_v1"] | {"referrer": "mobile", "utm_medium": "375x812", "browser": "email", "country": "Edge", "device_type": "US"} | | 3413 | add_to_cart | NULL | 2026-08-08 07:49:00 | {"product_id": 1007, "quantity": 2, "price": 58.49, "variant": "medium"} | ["returning", "campaign_b", "featured", "experiment_v2", "ios"] | {"utm_source": "fr", "session_id": "twitter", "os": "Safari", "language": "Windows", "screen_size": "FR"} | | 3472 | open | 779 | 2026-09-09 08:56:00 | {"action": "open", "value": 80} | ["new_user", "organic"] | {"utm_medium": "bing", "browser": "organic", "country": "iOS"} | | 3531 | crash | 876 | 2026-01-10 09:03:00 | {"action": "crash", "value": 90} | ["campaign_a", "referral", "experiment_v1"] | {"session_id": "direct", "os": "Firefox", "language": "macOS", "screen_size": "JP"} | | 3590 | error | 973 | 2026-02-11 10:10:00 | {"action": "error", "value": 100} | ["campaign_b", "featured", "experiment_v2", "ios"] | {"browser": "Chrome", "country": "Android", "device_type": "DE", "referrer": "de", "utm_medium": "tablet"} | ## Expected output First 4 of 4 rows: | referral_source | signup_count | purchase_count | conversion_rate | | --- | --- | --- | --- | | campaign_a | 1 | 0 | 0 | | organic | 4 | 1 | 0.25 | | other | 1 | 9 | 9 | | referral | 1 | 0 | 0 | ## Worked solution and explanation ### Why this problem exists in real interviews Growth analytics rarely hands you a clean `referral_source` column; it's buried in a tags/labels array, and the 'conversion' event is whatever counts as paid (here, `purchase`). This probes deriving a dimension with `CASE` over array membership, then computing a distinct-user ratio per group without letting a zero denominator blow up. --- ### Break down the requirements #### Step 1: Derive the referral source There is no `referral_source` column. Build one with a `CASE` that checks tag membership in priority order: organic > referral > campaign_a > campaign_b > other. #### Step 2: Count distinct signups and purchasers Restrict to the two events that matter: `WHERE event_type IN ('signup', 'purchase')`. Count DISTINCT users per event with conditional aggregation so one user with many events isn't double-counted. #### Step 3: Ratio, guarded `conversion_rate = distinct purchasers / distinct signups`. Keep only sources with at least one signup (`HAVING signup_count > 0`) so the denominator is always defined; round to 4 places. --- ### The solution **Derived-source signup-to-purchase conversion** ```sql WITH src 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') ) SELECT referral_source, COUNT(DISTINCT CASE WHEN event_type = 'signup' THEN user_id END) AS signup_count, COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END) AS purchase_count, ROUND(COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END) * 1.0 / COUNT(DISTINCT CASE WHEN event_type = 'signup' THEN user_id END), 4) AS conversion_rate FROM src GROUP BY referral_source HAVING COUNT(DISTINCT CASE WHEN event_type = 'signup' THEN user_id END) > 0 ORDER BY referral_source; ``` > **Cost Analysis** > > On a large `event_data`, filter to the two event types first so the scan and the GROUP BY work on a small slice. The `CASE` over tags is a per-row expression; a functional index on the derived source (or a materialized source column) would avoid recomputing it. > **Interviewers Watch For** > > Whether you guard the denominator. A source with purchases but no signups would divide by zero; the `HAVING signup_count > 0` (or a NULLIF) is the tell that you thought about it. > **Common Pitfall** > > Using `COUNT(*)` instead of `COUNT(DISTINCT user_id)`. A user with several signup or purchase events would be counted multiple times and the rate would be wrong. --- ## Common follow-up questions - How do you avoid a divide-by-zero for a source with purchases but no signups? _(Tests NULLIF / CASE guarding when a source has zero signups.)_ - A row's tags contain both "organic" and "campaign_a". Which source wins, and why? _(Probes that tag order matters when a row carries several source tokens; the CASE picks the first match.)_ - How would you measure conversion as 'signed up, then purchased later' for the same user? _(Pushes toward a true per-user funnel (signup THEN later purchase by the same user) using timestamps.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/signup_to_subscription_rate) - [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.