SQL | SOLVESQL 코딩테스트 : Lv2. (JOIN/UNION)

Lv2. 두 테이블 결합하기

https://solvesql.com/problems/join/

SELECT DISTINCT r.athlete_id
FROM records AS r 
  LEFT JOIN events AS e ON e.id = r.event_id
WHERE sport = 'Golf'

❗중복을 제거하라는 조건은 없었지만 DISTINCT를 해야 정답 처리 됨

 

Lv2. 한 번도 주문을 하지 않은 고객

https://solvesql.com/problems/fresh-users/

SELECT c.id
FROM customers as c
  LEFT JOIN orders AS o ON c.id = o.customer_id
WHERE o.id IS NULL
ORDER BY c.id ASC

 

Lv2. 김연아 선수가 출전한 올림픽

https://solvesql.com/problems/olympics-with-yuna/

SELECT g.year, g.season, g.city
FROM records AS r 
  INNER JOIN athletes AS a ON r.athlete_id = a.id
  INNER JOIN games AS g ON r.game_id = g.id
WHERE a.name = 'Yu-Na Kim'
ORDER BY g.year DESC

❗INNER JOIN 을 2번 사용해 테이블 전체 결합
❗WHERE 절로 조건 필터링

 

Lv2. 직속 상사 구하기

https://solvesql.com/problems/find-manager/

SELECT e1.employee_id, e1.name, e1.manager_id, e2.name AS manager_name
FROM employees AS e1
  LEFT JOIN employees AS e2 ON e1.manager_id = e2.employee_id
WHERE e1.manager_id IS NOT NULL
ORDER BY e1.employee_id ASC

❗셀프 조인 사용해서 상급자와 직원 결합하기

 

Lv2. 쇼핑몰의 일일 매출액

https://solvesql.com/problems/olist-daily-revenue/

SELECT DATE(ood.order_purchase_timestamp) AS dt 
  , ROUND(SUM(oopd.payment_value),2) AS revenue_daily
FROM olist_orders_dataset AS ood
  INNER JOIN olist_order_payments_dataset AS oopd ON ood.order_id = oopd.order_id
WHERE ood.order_purchase_timestamp > '2018-01-01'
GROUP BY DATE(ood.order_purchase_timestamp)
ORDER BY DATE(ood.order_purchase_timestamp) ASC

❗날짜시간 포맷을 날짜로만 사용하기 위해 DATE() 사용

 

Lv2. 싱가포르 고객의 주문 내역

https://solvesql.com/problems/orders-of-singapore-customers/

SELECT C.country
    , C.customer_id
    , O.order_date
    , O.order_id
FROM orders AS O
    INNER JOIN customers AS C ON O.customer_id = C.customer_id
WHERE C.country = 'Singapore'
ORDER BY O.order_date DESC

 

Lv2. 다음날도 서울숲의 미세먼지 농도는 나쁨 😢

https://solvesql.com/problems/bad-finedust-measure/

SELECT m1.measured_at AS today
  , m2.measured_at AS next_day
  , m1.pm10
  , m2.pm10 AS next_pm10
FROM measurements AS m1
  LEFT JOIN measurements AS m2 ON m2.measured_at = DATE_ADD(m1.measured_at, INTERVAL 1 DAY)
WHERE m1.pm10 < m2.pm10

❗SELF JOIN 사용해서 테이블 결합
❗당일과 다음날을 연결해야하므로 DATE_ADD() 사용해서 포맷 맞춰 테이블 결합