Find Campaign Purchases
Medium
You are given 3 tables, campaign, user and transaction with the following columns
campaign table:
user table:
transaction table
Write a SQL query that lists out the number of users for each campaign who were eligible for an upsell and purchased something.
WITH tran_user_campaign AS (
SELECT
c.upsell_campaign_id,
COUNT(DISTINCT CASE WHEN u.is_eligible_for_upsell_campaign = 1 THEN t.user_id END) AS eligible_users
FROM campaign c
LEFT JOIN "transaction" t ON t.transaction_date BETWEEN c.date_start AND c.date_end
LEFT JOIN user u ON t.user_id = u.user_id
WHERE u.is_eligible_for_upsell_campaign = 1
GROUP BY c.upsell_campaign_id
)
SELECT
upsell_campaign_id,
eligible_users
FROM tran_user_campaign;
SELECT upsell_campaign_id, COUNT(DISTINCT trans.user_id) AS eligible_users FROM campaign JOIN "transaction" AS trans ON transaction_date BETWEEN date_start AND date_end JOIN user ON trans.user_id = user.user_id WHERE is_eligible_for_upsell_campaign = 1 GROUP BY upsell_campaign_id