Campaign Click-to-Conversion

A user converts for a campaign if they clicked that campaign and have a conversion at or after one of those clicks.

For every campaign in campaigns:

- unique_clickers: distinct users who clicked it - converters: distinct clickers who converted for it - conversion_rate_pct: 100.0 * converters / unique_clickers, ROUND(x, 2), NULL when there are no clickers

Return columns campaign_id, unique_clickers, converters, conversion_rate_pct. Order by campaign_id ascending.

Schema

CREATE TABLE campaigns (
  campaign_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE clicks (
  click_id INTEGER PRIMARY KEY,
  campaign_id INTEGER NOT NULL,
  user_id INTEGER NOT NULL,
  clicked_at TEXT NOT NULL
);
CREATE TABLE conversions (
  conversion_id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  converted_at TEXT NOT NULL,
  revenue REAL NOT NULL
);

Sample data

campaigns

campaign_id	name
1	Search - Brand
2	Instagram Reels
3	Email Winback

clicks

click_id	campaign_id	user_id	clicked_at
1	1	100	2026-05-01 10:00:00
2	1	101	2026-05-01 11:00:00
3	1	100	2026-05-02 09:00:00
4	2	102	2026-05-02 12:00:00
5	2	103	2026-05-03 08:00:00
6	2	100	2026-05-04 15:00:00

conversions

conversion_id	user_id	converted_at	revenue
1	100	2026-05-03 18:00:00	49
2	102	2026-05-01 09:00:00	20
3	103	2026-05-03 08:00:00	15

Hint 1

Reduce clicks to one row per (campaign, user) first, with a 0/1 flag for "converted after some click".

Hint 2

Then LEFT JOIN from campaigns so campaigns without clicks still appear.

Good practice for: Digital Marketing Specialist, Data Analyst, Data Scientist, Product Manager

SQL (SQLite)

Run checks your query on the sample data (Ctrl + '). Submit also checks hidden databases.