Campaigns That Got Clicks but No Conversions — SQL Problem
Every tracked ad click belongs to a campaign, and a conversion (a purchase on the site) is credited to the click that brought the shopper in.
- Difficulty: Easy
- Topics: Joins, Subqueries
- Dialect: MySQL
- Problem: #275
Problem statement
Every tracked ad click belongs to a campaign, and a conversion (a purchase on the site) is credited to the click that brought the shopper in. The growth lead wants to pause campaigns that are buying traffic that never converts.
Return campaign_id and campaign_name of every campaign that has at least one click and no conversion on any of its clicks. Campaigns with no clicks at all are not in the answer. Return the rows in any order.
Tables
Table: Campaign
| Column | Type |
|---|---|
| campaign_id | int |
| campaign_name | varchar |
| channel | enum(search, social, display, video, email) |
Primary key: campaign_id.
One row per campaign.
Table: AdClick
| Column | Type |
|---|---|
| click_id | int |
| campaign_id | int |
| clicked_at | datetime |
Primary key: click_id.
One row per tracked click; campaign_id is always in Campaign.
Table: Conversion
| Column | Type |
|---|---|
| conversion_id | int |
| click_id | int |
| revenue | int |
Primary key: conversion_id.
One row per order credited to a click; click_id is always in AdClick. revenue is in rupees.
Examples
Example 1
Campaign
| campaign_id | campaign_name | channel |
|---|---|---|
| 1 | Diwali Mega Sale | search |
| 2 | Winter Skincare | social |
| 3 | Pongal Kitchen Week | display |
| 4 | Onam Festive Deals | video |
AdClick
| click_id | campaign_id | clicked_at |
|---|---|---|
| 101 | 1 | 2024-10-20 10:15:00 |
| 102 | 1 | 2024-10-20 11:02:00 |
| 103 | 2 | 2024-10-21 09:40:00 |
| 104 | 2 | 2024-10-21 21:05:00 |
| 105 | 4 | 2024-10-22 13:30:00 |
Conversion
| conversion_id | click_id | revenue |
|---|---|---|
| 1 | 102 | 2499 |
| 2 | 105 | 899 |
Output
| campaign_id | campaign_name |
|---|---|
| 2 | Winter Skincare |
How to solve Campaigns That Got Clicks but No Conversions
A conversion points at a click, and the click points at a campaign, so a campaign's conversions are found through AdClick. The answer is the campaigns with a click (semi join) and without a converted click (anti join).
EXISTS and NOT EXISTS say exactly that: the first subquery looks for any click of the campaign, the second for any click of the campaign that has a conversion row. The join version starts from the clicks — an inner join to AdClick already removes campaigns with no traffic — then LEFT JOINs the conversions and keeps the groups where COUNT(v.conversion_id) is zero, since COUNT of a column skips the NULLs a missing match produces.
NOT IN is safe here because the subquery's campaign_id is never NULL. With indexes on AdClick.campaign_id and Conversion.click_id, each form is a few lookups per campaign.
Reference solution (MySQL)
SELECT c.campaign_id, c.campaign_name
FROM Campaign c
WHERE EXISTS (SELECT 1 FROM AdClick k WHERE k.campaign_id = c.campaign_id)
AND NOT EXISTS (
SELECT 1 FROM AdClick k JOIN Conversion v ON v.click_id = k.click_id
WHERE k.campaign_id = c.campaign_id
)Another way
SELECT c.campaign_id, c.campaign_name
FROM Campaign c
JOIN AdClick k ON k.campaign_id = c.campaign_id
LEFT JOIN Conversion v ON v.click_id = k.click_id
GROUP BY c.campaign_id, c.campaign_name
HAVING COUNT(v.conversion_id) = 0Another way
SELECT campaign_id, campaign_name FROM Campaign
WHERE campaign_id IN (SELECT campaign_id FROM AdClick)
AND campaign_id NOT IN (SELECT k.campaign_id FROM AdClick k JOIN Conversion v ON v.click_id = k.click_id)← Click-Through Rate of Every Ad Group · Email Subject Lines Cut Off in the Inbox Preview →