FREE AI-Enhanced Online MySQL Compiler - For learning & practice

Untitled*

You must execute the code again to save those values in the database.

-- INIT database
CREATE TABLE campaign (
  id INT AUTO_INCREMENT KEY,
  name VARCHAR(100)
);

CREATE TABLE impressions (
  campaign_id INT,
  views INT,
  clicks INT,
  rollup_day INT
);

CREATE TABLE expenses (
  campaign_id INT,
  cost INT,
  rollup_day INT 
);

INSERT INTO campaign (name) 
values ('foo'), ('bar'), ('baz');

INSERT INTO impressions (campaign_id, views, clicks, rollup_day) 
VALUES 
	(1, 10, 3, 9998), (2, 7, 2, 9998), (3, 12, 4, 9998),
    (1, 7, 2, 9999), (2, 9, 1, 9999), (3, 11, 5, 9999),
    (1, 7, 4, 10000), (2, 9, 3, 10000), (3, 11, 3, 10000);

INSERT INTO expenses (campaign_id, cost, rollup_day) 
VALUES 
	(1, 5, 9998), (2, 6, 9998), (3, 5, 9998),
    (1, 4, 9999), (2, 2, 9999), (3, 8, 9999),
    (1, 3, 10000), (2, 3, 10000), (3, 7, 10000);

-- QUERY database

SELECT
	campaign.id, 
    campaign.name, 
    impressions.views, 
    impressions.clicks, 
    expenses.cost total_cost, 
    (expenses.cost / impressions.clicks) cost_per_click,
    expenses_yesterday.cost total_cost_yesterday,
    (expenses_yesterday.cost / impressions_yesterday.clicks) cost_per_click_yesterday
FROM campaign
INNER JOIN impressions 
	ON impressions.campaign_id = campaign.id 
    AND impressions.rollup_day=(SELECT MAX(rollup_day) FROM impressions)
INNER JOIN expenses 
	ON expenses.campaign_id = impressions.campaign_id 
    AND expenses.rollup_day=impressions.rollup_day
LEFT JOIN impressions impressions_yesterday 
	ON impressions_yesterday.campaign_id = campaign.id 
    AND impressions_yesterday.rollup_day=impressions.rollup_day -1
LEFT JOIN expenses expenses_yesterday 
	ON expenses_yesterday.campaign_id = impressions.campaign_id 
    AND expenses_yesterday.rollup_day=impressions.rollup_day -1

id	name	views	clicks	total_cost	cost_per_click	total_cost_yesterday	cost_per_click_yesterday
1	foo	7	4	3	0.7500	4	2.0000
2	bar	9	3	3	1.0000	2	2.0000
3	baz	11	3	7	2.3333	8	1.6000