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;