🌻

시현

1~5번 문제

#1.
SELECT sc.campaign_id, sc.campaign_name, sc.campaign_type, COUNT(*) cnt_ad
FROM sql_ad_stats sas INNER JOIN sql_campaigns sc ON sas.campaign_id = sc.campaign_id
GROUP BY sc.campaign_id, sc.campaign_name
;

#2.
SELECT sc.campaign_id, sc.campaign_name, sc.campaign_type, SUM(sas.clicks) cnt_clicks
FROM sql_ad_stats sas INNER JOIN sql_campaigns sc ON sas.campaign_id = sc.campaign_id
GROUP BY sc.campaign_id, sc.campaign_name
;

#3.
SELECT  sc.campaign_id, sc.campaign_name, sc.campaign_type, ROUND(AVG(sas.cost)) avg_cost
FROM sql_ad_stats sas INNER JOIN sql_campaigns sc ON sas.campaign_id = sc.campaign_id
GROUP BY  sc.campaign_id, sc.campaign_name
;

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

#5.
SELECT sas.date, sas.campaign_id, campaign_name, SUM(sas.clicks) cnt_clicks
FROM sql_campaigns sc INNER JOIN sql_ad_stats sas ON sc.campaign_id = sas.campaign_id
GROUP BY sas.date
ORDER BY sas.date
;
SQL
복사

6~10번 문제

#6.
SELECT sc.campaign_id, sc.campaign_name,
       ROUND(SUM(sas.clicks)/SUM(sas.impressions)*100,2) CTR
FROM sql_ad_stats sas INNER JOIN sql_campaigns sc ON sas.campaign_id = sc.campaign_id
GROUP BY sc.campaign_id, sc.campaign_name
ORDER BY CTR DESC
;

#7.

SELECT sc.campaign_id, sc.campaign_name,
        ROUND(SUM(sas.cost)/SUM(sas.clicks)) CPC
FROM sql_ad_stats sas INNER JOIN sql_campaigns sc ON sas.campaign_id = sc.campaign_id
GROUP BY campaign_id, campaign_name
;

#8.
SELECT sas.campaign_id, 
       sc.campaign_name, 
       date, 
       AVG(clicks) avg_clicks
FROM sql_ad_stats sas INNER JOIN sql_campaigns sc ON sas.campaign_id = sc.campaign_id
GROUP BY sas.campaign_id, sc.campaign_name
HAVING avg_clicks >= 300
ORDER BY sas.campaign_id

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

#10.
SELECT sp.product_id , sp.product_name, COUNT(DISTINCT campaign_id) campaign_cnt
FROM sql_products sp INNER JOIN sql_ad_stats sas ON sp.product_id = sas.product_id
GROUP BY sp.product_id 
ORDER BY sp.product_id
;
SQL
복사

11~15번 문제

11.
SELECT  campaign_id, channel_name, COUNT(Distinct sc.channel_id) channel_cnt
FROM sql_channels sc INNER JOIN sql_ad_stats sas ON sc.channel_id = sas.channel_id
GROUP BY campaign_id
;

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

#13.
SELECT sc.campaign_id, campaign_name, sas.product_id, product_name, AVG(clicks) avg_clicks
FROM sql_campaigns sc INNER JOIN sql_ad_stats sas ON sc.campaign_id = sas.campaign_id
INNER JOIN sql_products sp ON sas.product_id = sp.product_id
GROUP BY sc.campaign_id, sas.product_id
ORDER BY avg_clicks DESC LIMIT 3
;

#14.
SELECT sc.campaign_id, campaign_name, ROUND(SUM(clicks)/SUM(impressions)*100,2) CTR
FROM sql_campaigns sc INNER JOIN sql_ad_stats sas ON sc.campaign_id = sas.campaign_id
GROUP BY campaign_id, campaign_name
HAVING (CTR > (SELECT SUM(clicks)/SUM(impressions)*100 FROM sql_ad_stats sas))
;


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