🌻

주연

1~5번 문제

1. 
select sc.campaign_id, 
sc.campaign_name,
count(sas.stat_id) as cnt_data
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

2. 
select sc.campaign_id,
sc.campaign_name,
sum(sas.clicks) as sum_cilcks
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

3. 
select sc.campaign_id, 
sc.campaign_name,
avg(sas.cost) as avg_cost
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

4. 
select sc.campaign_id,
sum(sas.clicks) sum_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 sum_clicks desc
LIMIT 1

5. 
select date,
sum(clicks) sum_clicks
from sql_ad_stats sas 
group by 1
order by sum_clicks desc

SQL
복사

6~10번 문제

6. 
SELECT 
  sc.campaign_id, 
  sc.campaign_name,
  SUM(sas.clicks) AS sum_clicks,
  SUM(sas.impressions) AS sum_impressions,
  (SUM(sas.clicks) * 100.0 / NULLIF(SUM(sas.impressions), 0)) AS ctr
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 
  ctr DESC
  
7. 
SELECT 
  sc.campaign_id,
  sc.campaign_name,
  (SUM(sas.cost) / NULLIF(SUM(sas.clicks), 0)) AS avg_cpc
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
  
  8. 
  SELECT 
  sc.campaign_id,
  sc.campaign_name,
  COUNT(DISTINCT sas.date) AS sas_date,
  SUM(sas.clicks) AS sas_clicks,
  (SUM(sas.clicks) * 1.0 / COUNT(DISTINCT sas.date)) AS avg_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
HAVING 
  avg_clicks>= 300
  
  9. 
  SELECT DISTINCT 
  sc.campaign_id,
  sc.campaign_name
FROM  
  sql_campaigns sc
INNER JOIN 
  sql_ad_stats sas 
  ON sc.campaign_id = sas.campaign_id
WHERE 
  sas.clicks >= 1000
  
  10. 
   select sp.product_id, sp.product_name,
count(sc.campaign_id) cnt_sc
from sql_products sp 
inner join sql_ad_stats sas on sp.product_id = sas.product_id
inner join sql_campaigns sc on sc.campaign_id = sas.campaign_id
group by product_id, product_name
SQL
복사

11~15번 문제

11. 
select sc.campaign_id,sc.campaign_name,
count(distinct sc2.channel_id)
from sql_campaigns sc 
inner join sql_channels sc2 
group by sc.campaign_id, sc.campaign_name

12. 
SELECT 
sc.campaign_id,
ROUND(SUM(sas.clicks) / SUM(sas.impressions) * 100, 2) AS avg_ctr
FROM sql_ad_stats sas 
JOIN sql_campaigns sc  ON sas.campaign_id = sc.campaign_id
group by sc.campaign_id 
order by avg_ctr
LIMIT 1

13. 
select sc.campaign_id,
sc.campaign_name, 
sp.product_id,
sp.product_name,
avg(sas.clicks) as avg_clicks
from sql_campaigns sc
inner join  sql_ad_stats sas on sas.campaign_id = sc.campaign_id
inner join sql_products sp  on  sp.product_id = sas.product_id
group by sc.campaign_id,sc.campaign_name, sp.product_id,sp.product_name
order by avg_clicks desc
LIMIT 3

14. 
SELECT 
  sc.campaign_name,
  sc.campaign_id,
  ROUND(SUM(sas.clicks) * 100.0 / SUM(sas.impressions), 2) AS avg_ctr
FROM sql_ad_stats sas
JOIN sql_campaigns sc ON sas.campaign_id = sc.campaign_id
GROUP BY sc.campaign_name, sc.campaign_id
HAVING 
  ROUND(SUM(sas.clicks) * 100.0 / SUM(sas.impressions), 2) >
  (
    SELECT 
      ROUND(SUM(clicks) * 100.0 / SUM(impressions), 2)
    FROM sql_ad_stats
  )
ORDER BY avg_ctr DESC

15.
SELECT sc.campaign_id, sas.date, sas.clicks 
from sql_campaigns sc 
inner join sql_ad_stats sas on sc.campaign_id = sas.campaign_id
inner join (
select campaign_id, max(clicks) as max_clicks
from sql_ad_stats sas2 
group by campaign_id
  )  
max_c_campaign
ON sas.campaign_id = max_c_campaign.campaign_id
AND sas.clicks = max_c_campaign.max_clicks
SQL
복사