Testing the Database
--Displays the Ads associated with each Campaign and Company
SELECT Ad_Name,
c.Campaign_ID,
Campaign_Name,
CompanyName
FROM Campaigns c
LEFT JOIN Advertisements a
ON c.Campaign_ID = a.Campaign_ID
LEFT JOIN Clients cl
ON c.Client_ID = cl.Client_ID;
--Highest performing Platform (conversion rate) and Metrics for each Platform
SELECT Platform_ID,
Platform_Name,
Impressions,
Clicks,
Conversion_Rate
FROM Campaigns c
LEFT JOIN Platforms p
ON c.Campaign_ID = p.Campaign_ID
LEFT JOIN Metrics m
ON m.Campaign_ID = c.Campaign_ID
GROUP BY Platform_ID
ORDER BY m.Conversion_Rate DESC;
--How many Campaigns each Employee manages
SELECT Emp_ID,
First_Name,
Last_Name,
COUNT(*) AS total_campaigns
FROM Campaigns c
LEFT JOIN Employees e
ON e.Supervisor = c.Manager_ID
GROUP BY Emp_ID;
--Calculates Cost Per Click for each Campaign
SELECT c.Campaign_ID,
(m.Clicks/p.Amount) AS cost_per_click
FROM Campaigns c
LEFT JOIN Payments p
ON p.Campaign_ID = c.Campaign_ID
LEFT JOIN Metrics m
ON m.Campaign_ID=c.Campaign_ID;