[Solvesql] 데이터리안 SQL 캠프 실전반 공개문제/정답 모음

 

목차

[☆☆] 다음날도 서울숲의 미세먼지 농도는 나쁨 😢

서울숲 일별 평균 대기오염도 데이터셋은 2022년 서울숲 대기오염도 측정소에서 매일 기록한 대기오염 정보를 담고 있습니다.

measurements 테이블의 pm10 컬럼에는 다양한 대기오염도 측정 기준 중에서도 미세먼지(PM10) 농도가 기록되어 있습니다. 이 데이터를 이용하여 당일의 미세먼지 농도보다 바로 다음날의 미세먼지 농도가 더 안좋은 날을 찾아주세요. 결과는 아래 컬럼들을 포함해야 합니다.

• today: 당일 (YYYY-MM-DD)
• next_day: 다음날 (YYYY-MM-DD)
• pm10: 당일의 미세먼지 농도
• next_pm10: 다음날의 미세먼지 농도

SELECT
  strftime('%Y-%m-%d', m1.measured_at) AS today,
  strftime('%Y-%m-%d', m2.measured_at) AS next_day,
  m1.pm10 AS pm10,
  m2.pm10 AS next_pm10
FROM
  measurements m1
  join measurements m2 on m1.measured_at = date(m2.measured_at, '-1 day')
WHERE
  m2.pm10 > m1.pm10

 

 

[☆☆☆☆☆] 카테고리 별 매출 비율

US E-Commerce Records 2020 데이터셋은 미국 이커머스 웹사이트의 판매 데이터 입니다. records 테이블에는 어느 온라인 쇼핑몰의 주문 데이터 입니다. 주문된 상품의 카테고리(category), 서브 카테고리(sub_category), 매출액(sales) 등의 정보가 들어있습니다.

서브 카테고리 별 매출액을 계산하고 그 매출액이 각 서브 카테고리가 속해있는 카테고리 안에서 비중을 얼마나 차지하는지, 그리고 전체 매출액에서는 비중을 얼마나 차지하는지 계산하는 쿼리를 작성해주세요. 쿼리 결과는 아래 컬럼을 포함해야 하고, 모든 수치 데이터는 소수점 아래 셋째 자리에서 반올림 해 둘째 자리까지 표현해주세요.

• category - 카테고리 이름
• sub_category - 서브 카테고리 이름
• sales_sub_category - 서브 카테고리 별 매출액의 합계
• sales_category - 카테고리 별 매출액의 합계
• sales_total - 전체 매출액
• pct_in_category - 카테고리 매출 중 해당 서브 카테고리 매출의 비율 (%)
• pct_in_total - 전체 매출 중 해당 서브 카테고리 매출의 비율 (%)

(1) OVER(PARTITION BY)를 사용하는 경우

WITH sub AS (
    SELECT 
        category,
        sub_category,
        SUM(sales) AS sum_sales
    FROM records
    GROUP BY category, sub_category
)
SELECT 
    category,
    sub_category,
    ROUND(SUM(sum_sales) OVER(PARTITION BY sub_category), 2) AS sales_sub_category,
    ROUND(SUM(sum_sales) OVER(PARTITION BY category), 2) AS sales_category,
    ROUND(SUM(sum_sales) OVER(), 2) AS sales_total,
    ROUND(SUM(sum_sales) OVER(PARTITION BY sub_category) / SUM(sum_sales) OVER(PARTITION BY category) * 100, 2) AS pct_in_category,
    ROUND(SUM(sum_sales) OVER(PARTITION BY sub_category) / SUM(sum_sales) OVER() * 100, 2) AS pct_in_total
FROM sub;

 

 

(2) GROUP BY를 사용하는 경우 (OVER 함수 X)

WITH
  c AS (
    SELECT
      category,
      sum(sales) as sales_category
    FROM records
    GROUP BY category
  ),
  t AS (
    SELECT
      r.category,
      r.sub_category,
      ROUND(sum(r.sales), 2) as sales_sub_category,
      ROUND(c.sales_category, 2) as sales_category,
      ROUND(
        (
          SELECT
            SEUM(sales)
          FROM
            records
        ),
        2
      ) AS sales_total
    FROM
      records r JOIN c USING (category)
    GROUP BY category,sub_category
  )
SELECT
  category,
  sub_category,
  sales_sub_category,
  sales_category,
  sales_total,
  ROUND(sales_sub_category*100/sales_category,2) AS pct_in_category,
  ROUND(sales_sub_category*100/sales_total,2) AS pct_in_total
FROM
  t