🌻

정민

1~5번 문제

1
SELECT sc.campaign_id,
		sc.campaign_name,
		COUNT(*)
FROM sql_campaigns sc 
LEFT JOIN sql_ad_stats sas 
ON sc.campaign_id = sas.campaign_id
GROUP BY campaign_id 

2
SELECT sc.campaign_id,
		sc.campaign_name,
		SUM(clicks)
FROM sql_campaigns sc 
LEFT JOIN sql_ad_stats sas
ON sc.campaign_id = sas.campaign_id
GROUP BY sc.campaign_id  

3
SELECT sc.campaign_id,
		sc.campaign_name,
		AVG(cost)
FROM sql_campaigns sc 
LEFT JOIN sql_ad_stats sas
ON sc.campaign_id = sas.campaign_id
GROUP BY sc.campaign_id 

4
SELECT sc.campaign_id,
		sc.campaign_name,
		SUM(clicks)
FROM sql_campaigns sc 
LEFT JOIN sql_ad_stats sas 
ON sc.campaign_id = sas.campaign_id 
GROUP BY campaign_id 
ORDER BY SUM(clicks) DESC LIMIT 1

5
SELECT a.date,
		sc.campaign_name,
		a.max_ck
FROM sql_campaigns sc 
LEFT JOIN 
(SELECT sas.campaign_id,
		sas.date,
MAX(sas.clicks) AS max_ck
FROM sql_ad_stats sas 
GROUP BY campaign_id
) AS a
ON sc.campaign_id =a.campaign_id
GROUP BY a.campaign_id, date
ORDER BY a.date
SQL
복사

6~10번 문제

6.
SELECT sc.campaign_id,
		sc.campaign_name,
		(SUM(sas.clicks)/SUM(sas.impressions)) * 100 AS ctr
FROM sql_campaigns sc 
LEFT JOIN sql_ad_stats sas 
ON sc.campaign_id = sas.campaign_id
GROUP BY campaign_id 
ORDER BY ctr DESC

7.
SELECT sc.campaign_id,
		sc.campaign_name,
		SUM(sas.cost)/SUM(sas.clicks) AS cpc
FROM sql_campaigns sc 
LEFT JOIN sql_ad_stats sas 
ON sc.campaign_id = sas.campaign_id
GROUP BY campaign_id 

8.
SELECT sc.campaign_name,
		ROUND(SUM(clicks)/COUNT(sc.campaign_id),0) AS avg_clk
FROM sql_campaigns sc 
LEFT JOIN sql_ad_stats sas 
ON sc.campaign_id = sas.campaign_id
GROUP BY sas.campaign_id
HAVING avg_clk >=300

9.
SELECT sc.campaign_id,
		sc.campaign_name,
		sas.clicks
FROM sql_campaigns sc 
LEFT JOIN sql_ad_stats sas 
ON sc.campaign_id = sas.campaign_id
WHERE sas.clicks >= 1000

10.
SELECT sp.product_id,
		COUNT(DISTINCT campaign_id)
FROM sql_products sp 
LEFT JOIN sql_ad_stats sas 
ON sp.product_id = sas.product_id
GROUP BY sp.product_id
SQL
복사

11~15번 문제

11.
SELECT sc.campaign_id,
		sc.campaign_name,
		COUNT(DISTINCT sas.channel_id)
FROM sql_campaigns sc 
LEFT JOIN sql_ad_stats sas 
ON sc.campaign_id = sas.campaign_id
GROUP BY sc.campaign_id

12.
SELECT sc.campaign_id,
		sc.campaign_name,
		sas.date,
		(SUM(sas.clicks)/SUM(sas.impressions)) * 100 AS ctr
FROM sql_campaigns sc 
LEFT JOIN sql_ad_stats sas 
ON sc.campaign_id = sas.campaign_id
GROUP BY campaign_id 
ORDER BY ctr DESC LIMIT 1

13.
SELECT sp.product_name,
		a.campaign_name,
		a.click
FROM sql_products sp 
LEFT JOIN
(SELECT sas.product_id,
		sc.campaign_name,
		sc.campaign_id,
		AVG(clicks) AS click
FROM sql_ad_stats sas 
LEFT JOIN sql_campaigns sc
ON sas.campaign_id = sc.campaign_id
GROUP BY product_id , campaign_id 
ORDER BY campaign_id 
) AS a
ON sp.product_id =a.product_id
ORDER BY a.click DESC LIMIT 3

14.

15.
SELECT sc.campaign_name,
		MAX(sas.clicks),
		sas.date
FROM sql_campaigns sc 
LEFT JOIN sql_ad_stats sas 
ON sc.campaign_id = sas.campaign_id
GROUP BY sc.campaign_id 
SQL
복사