1~5번 문제
#1.각 캠페인별로 광고가 집행된 총 횟수(행 수)를 구하세요.
SELECT sc.campaign_name,
COUNT(*) AS '광고 집행 횟수'
FROM sql_campaigns sc
JOIN sql_ad_stats sas
ON sc.campaign_id = sas.campaign_id
GROUP BY 1
#2.캠페인별 총 클릭 수(clicks)를 구하세요.
SELECT campaign_name,
SUM(sas.clicks) AS '총 클릭수'
FROM sql_campaigns sc
JOIN sql_ad_stats sas
ON sc.campaign_id = sas.campaign_id
GROUP BY 1
#3.캠페인별 평균 비용(cost)을 구하세요.
SELECT campaign_name,
ROUND(AVG(cost)) AS avg_cost
FROM sql_campaigns sc
JOIN sql_ad_stats sas
ON sc.campaign_id = sas.campaign_id
GROUP BY 1
#4.가장 많은 클릭 수를 기록한 캠페인의 ID를 구하세요.
SELECT campaign_name,
SUM(sas.clicks) AS '총 클릭수',
sc.campaign_id
FROM sql_campaigns sc
JOIN sql_ad_stats sas
ON sc.campaign_id = sas.campaign_id
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1
#5.날짜(date)별 전체 광고 클릭 수를 구하세요.
SELECT sas.date,
SUM(clicks) AS sum_clicks
FROM sql_campaigns sc
JOIN sql_ad_stats sas
ON sc.campaign_id = sas.campaign_id
GROUP BY 1
ORDER BY 1
SQL
복사
6~10번 문제
#중급
#1.캠페인별 클릭률(CTR: clicks / impressions * 100)을 구하고 내림차순 정렬하세요.
SELECT sc.campaign_name,
(sum(clicks)/sum(impressions))*100 AS 'CTR'
FROM sql_campaigns sc
JOIN sql_ad_stats sas
ON sc.campaign_id = sas.campaign_id
GROUP BY sc.campaign_id
ORDER BY 2 desc
#2.캠페인별 평균 CPC(클릭당 비용)를 구하세요.
SELECT sc.campaign_name,
(sum(sas.cost)/sum(sas.clicks)) AS avg_cpc
FROM sql_campaigns sc
JOIN sql_ad_stats sas
ON sc.campaign_id = sas.campaign_id
GROUP BY 1
#3.하루 평균 클릭 수가 300 이상인 캠페인을 구하세요.
SELECT sc.campaign_name,
AVG(sas.clicks) AS avg_clicks
FROM sql_campaigns sc
JOIN sql_ad_stats sas
ON sc.campaign_id = sas.campaign_id
GROUP BY 1
HAVING avg_clicks>=300
#4.한 번이라도 1000클릭 이상 발생한 캠페인을 구하세요.
SELECT sc.campaign_name, sas.clicks
FROM sql_campaigns sc
JOIN sql_ad_stats sas
ON sc.campaign_id = sas.campaign_id
WHERE sas.clicks>=1000
#5.제품(product_id)별 캠페인 수를 구하세요.
SELECT sp.product_id, sp.product_name,
COUNT(DISTINCT sas.campaign_id) AS cnt_campaign_id
FROM sql_products sp
JOIN sql_ad_stats sas
ON sp.product_id = sas.product_id
GROUP BY 1
SQL
복사
11~15번 문제
#고급
#1.캠페인별 채널 수(channel_id 기준 중복 제거)를 구하세요.
SELECT sc.campaign_name,
COUNT(DISTINCT sas.channel_id) cnt_channel_id
FROM sql_campaigns sc
JOIN sql_ad_stats sas
ON sc.campaign_id = sas.campaign_id
JOIN sql_channels sc2
ON sas.channel_id = sc2.channel_id
GROUP BY 1
ORDER BY 1
#2.가장 높은 CTR을 기록한 날짜와 캠페인을 구하세요.
SELECT sc.campaign_name, sas.date,
(sum(sas.clicks)/sum(impressions))*100 AS CTR
FROM sql_campaigns sc
JOIN sql_ad_stats sas
ON sc.campaign_id = sas.campaign_id
GROUP BY 1,2
ORDER BY 3 DESC
LIMIT 1
#3.캠페인-제품 조합 중 평균 클릭 수가 가장 높은 상위 3개 조합을 구하세요.
SELECT sc.campaign_id,sc.campaign_name,sp.product_name,
AVG(sas.clicks) AS avg_click
FROM sql_campaigns sc
JOIN sql_ad_stats sas
ON sc.campaign_id = sas.campaign_id
JOIN sql_products sp
ON sas.product_id = sp.product_id
GROUP BY 1,3
ORDER BY avg_click DESC
LIMIT 3
#4.전체 기간 중 평균 CTR보다 높은 캠페인만 조회하세요.
#캠페인 별 CTR 계산
SELECT sc.campaign_name,
(sum(clicks)/sum(impressions))*100 AS ctr
FROM sql_ad_stats sas
JOIN sql_campaigns sc
ON sas.campaign_id = sc.campaign_id
GROUP BY 1
HAVING ctr >
(#--전체 평균 CTR이랑 비교!
SELECT (SUM(clicks)/SUM(impressions)) *100
FROM sql_ad_stats
)
#5.날짜(date)별 전체 광고 클릭 수를 구하세요.
SELECT sc.campaign_name, sas.date, sas.clicks AS max_today_click
FROM sql_ad_stats sas
JOIN sql_campaigns sc
ON sas.campaign_id = sc.campaign_id
JOIN
(
SELECT sas.campaign_id,
MAX(sas.clicks) max_cks
FROM sql_ad_stats sas
GROUP BY 1
) a
ON sas.campaign_id = a.campaign_id
AND sas.clicks = a.max_cks
SQL
복사