SQL | SOLVESQL 코딩테스트 : Lv3. (CASE/IF)

Lv3. 지역별 주문의 특징

https://solvesql.com/problems/characteristics-of-orders/

SELECT region AS Region
  , COUNT(DISTINCT CASE WHEN category = 'Furniture' THEN order_id END) AS 'Furniture'
  , COUNT(DISTINCT CASE WHEN category = 'Office Supplies' THEN order_id END) AS 'Office Supplies'
  , COUNT(DISTINCT CASE WHEN category = 'Technology' THEN order_id END) AS 'Technology'
FROM records
GROUP BY region
ORDER BY region ASC

❗CASE WHEN 사용해서 칼럼 만들기
❗COUNT(DISTINCT) 사용해서 고유 ORDER_ID 기준으로 갯수 세기

 

Lv3. 배송 예정일 예측 성공과 실패

https://solvesql.com/problems/estimated-delivery-date/

SELECT DATE(order_purchase_timestamp) AS purchase_date
  , COUNT(CASE WHEN order_delivered_customer_date <= order_estimated_delivery_date THEN order_delivered_customer_date END) AS success
  , COUNT(CASE WHEN order_delivered_customer_date > order_estimated_delivery_date THEN order_delivered_customer_date END) AS fail
FROM olist_orders_dataset
WHERE order_purchase_timestamp BETWEEN '2017-01-01 00:00:00' AND '2017-01-31 23:59:59'
  AND order_delivered_customer_date IS NOT NULL
  AND order_estimated_delivery_date IS NOT NULL
GROUP BY DATE(order_purchase_timestamp)
ORDER BY DATE(order_purchase_timestamp) ASC

❗CASE WHEN, 비교 연산자 사용해서 조건에 맞춰 칼럼 생성
❗WHERE 절에 조건 충족 시키기

 

Lv3. Amy는 이 영화를 어디서 볼까?

https://solvesql.com/problems/ott-used-by-amy/

SELECT title, year, genres, directors
  , CASE
      WHEN netflix = 1 THEN 'netflix'
      WHEN prime_video = 1 THEN 'prime_video'
      WHEN disney_plus = 1 THEN 'disney_plus'
      ELSE 'hulu'
    END AS platform
FROM movies
WHERE year = 2021
ORDER BY title ASC

❗CASE WHEN 사용해서 platform 칼럼 생성
❗WHERE 절에 조건 충족

 

Lv3. 바겐 세일!

https://solvesql.com/problems/bargain-sale/

SELECT order_date
  , SUM(CASE WHEN discount >= 0.8 THEN quantity END) AS big_discount_items
  , SUM(quantity) AS all_items
FROM records
GROUP BY order_date
HAVING big_discount_items IS NOT NULL
  AND all_items >= 10
ORDER BY big_discount_items DESC

❗CASE WHEN 사용해서 칼럼 생성
❗SUM() 집계 함수 사용해서 갯수 세기
❗HAVING 절에 조건 넣기 (GROUP BY 이후 조건이기에 HAVING 사용)