Campaigns That Got Clicks but No Conversions

Easy
275.

Campaigns That Got Clicks but No Conversions

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

ColumnTypeValues
campaign_idint
campaign_namevarchar
channelenumsearch, social, display, video, email

Primary key: campaign_id.

One row per campaign.

Table: AdClick

ColumnType
click_idint
campaign_idint
clicked_atdatetime

Primary key: click_id.

One row per tracked click; campaign_id is always in Campaign.

Table: Conversion

ColumnType
conversion_idint
click_idint
revenueint

Primary key: conversion_id.

One row per order credited to a click; click_id is always in AdClick. revenue is in rupees.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Campaign

campaign_idcampaign_namechannel
1Diwali Mega Salesearch
2Winter Skincaresocial
3Pongal Kitchen Weekdisplay
4Onam Festive Dealsvideo

AdClick

click_idcampaign_idclicked_at
10112024-10-20 10:15:00
10212024-10-20 11:02:00
10322024-10-21 09:40:00
10422024-10-21 21:05:00
10542024-10-22 13:30:00

Conversion

conversion_idclick_idrevenue
11022499
2105899

Output

campaign_idcampaign_name
2Winter Skincare
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
campaign_idint
campaign_namevarchar
channelenum(search, social, display, video, email)

Primary key: campaign_id.

One row per campaign.

Table: AdClick

ColumnType
click_idint
campaign_idint
clicked_atdatetime

Primary key: click_id.

One row per tracked click; campaign_id is always in Campaign.

Table: Conversion

ColumnType
conversion_idint
click_idint
revenueint

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_idcampaign_namechannel
1Diwali Mega Salesearch
2Winter Skincaresocial
3Pongal Kitchen Weekdisplay
4Onam Festive Dealsvideo

AdClick

click_idcampaign_idclicked_at
10112024-10-20 10:15:00
10212024-10-20 11:02:00
10322024-10-21 09:40:00
10422024-10-21 21:05:00
10542024-10-22 13:30:00

Conversion

conversion_idclick_idrevenue
11022499
2105899

Output

campaign_idcampaign_name
2Winter 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) = 0

Another 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 →