# The Conversion Story

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

Canonical URL: <https://datadriven.io/problems/the_conversion_story>

Domain: SQL · Difficulty: medium · Seniority: L5

## 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. Round the rate to 4 decimal places.

## 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/the_conversion_story)
- [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.