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

Lv3. 복수 국적 메달 수상한 선수 찾기

https://solvesql.com/problems/multiple-medalist/

SELECT A.NAME -- A.ID,COUNT(DISTINCT R.team_id)
FROM athletes as A 
  INNER JOIN records AS R ON A.ID = R.athlete_id
  INNER JOIN games AS G ON R.game_id = G.id
  INNER JOIN teams AS T ON R.team_id = T.id
WHERE G.year >= 2000
  AND R.medal IS NOT NULL
GROUP BY A.ID
HAVING COUNT(DISTINCT R.team_id) > 1
ORDER BY A.NAME ASC

❗INNER JOIN으로 테이블 결합
❗WHERE 절에 년도 필터링, 메달 필터링
❗GROUP BY 절에 선수 id 사용
❗HAVING 절에 DISTINCT TEAM_ID 사용 (국적이 다른 경우를 뽑아야 함)

❗참고 : https://monamienamie.tistory.com/20

 

Lv3. 쇼핑몰의 일일 매출액과 ARPPU

https://solvesql.com/problems/daily-arppu/

SELECT DATE_FORMAT(ood.order_purchase_timestamp,'%Y-%m-%d') AS dt
  , COUNT(DISTINCT ood.order_id) AS pu
  , ROUND(SUM(oopd.payment_value),2) AS revenue_daily
  , ROUND(SUM(oopd.payment_value) / COUNT(DISTINCT ood.order_id),2) AS arppu
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_FORMAT(ood.order_purchase_timestamp,'%Y-%m-%d')
ORDER BY DATE_FORMAT(ood.order_purchase_timestamp,'%Y-%m-%d') ASC

❗DATE_FORMAT()으로 날짜 포맷 바꾸기
❗집계 함수를 사용해서 조건 충족 (COUNT()는 DISTINCT를 사용해서 ORDER_ID 중복 집계 방지하기)
❗INNER JOIN 을 통한 테이블 결합
❗WHERE 절에 날짜 조건 필터링
❗GROUP BY 절에 조건 넣기

 

Lv3. 멘토링 짝꿍 리스트

https://solvesql.com/problems/mentor-mentee-list/

WITH MENTEE AS (
  SELECT *
  FROM employees
  WHERE join_date >= DATE_SUB('2021-12-31', INTERVAL 3 MONTH)
), MENTOR AS (
  SELECT *
  FROM employees
  WHERE join_date <= DATE_SUB('2021-12-31', INTERVAL 2 YEAR)
)

SELECT MENTEE.employee_id AS mentee_id
  , MENTEE.NAME AS mentee_name
  , MENTOR.employee_id AS mentor_id
  , MENTOR.name AS mentor_name
FROM MENTEE
  CROSS JOIN MENTOR
WHERE MENTEE.department != MENTOR.department
ORDER BY mentee_id ASC, mentor_id ASC

❗CROSS JOIN 사용해서 모든 경우의 수에 해당하는 조합 만들기
❗WHERE 절에 조건 사용해서 부서가 다른 경우의 수 조건 걸기
❗참고 : https://nowolver.tistory.com/36

+) WITH 절

❗멘토와 멘티 조건에 따라 데이터 분리하기
❗WHERE 절에 멘토, 멘티 조건 분리

 

Lv3. 작품이 없는 작가 찾기

https://solvesql.com/problems/artists-without-artworks/

SELECT artist_id, name
FROM artists
WHERE artist_id NOT IN (SELECT artist_id FROM artworks_artists)
  AND death_year IS NOT NULL

❗사망 년도가 비어있지 않아야 해서 IS NOT NULL 사용

+) WHERE 절 서브쿼리

❗전시 작품이 있는 ARTIST_ID 만 추출
❗이후 WHERE 절에서 없는 ID만 추출

 

Lv3. 오스트리아 고객들의 환불 금액

https://solvesql.com/problems/refund-austria/

SELECT SUM(ABS(oi.price * oi.quantity)) AS refund_austria
FROM customers AS c
  INNER JOIN orders AS o ON c.customer_id = o.customer_id
  INNER JOIN order_items AS oi ON o.order_id = oi.order_id
WHERE o.order_id LIKE 'C%'
  AND c.country = 'Austria'

❗ABS() 사용해서 절댓값으로 계산
❗INNER JOIN 사용해서 테이블 결합
❗WHERE 절에 LIKE 사용해서 환불 데이터 선택

 

Lv3. 국가 별 판매 금액

https://solvesql.com/problems/sales-per-country/

WITH T1 AS (
  SELECT c.*, o.order_id, oi.price, oi.quantity
  FROM customers AS c
    INNER JOIN orders AS o ON c.customer_id = o.customer_id
    INNER JOIN order_items AS oi ON o.order_id = oi.order_id
  WHERE o.order_date BETWEEN '2019-01-01' AND '2019-01-31'
)

SELECT country
  , SUM(price * quantity) AS sales
FROM T1
WHERE order_id NOT LIKE 'C%'
GROUP BY country
ORDER BY sales DESC

❗WHERE 절에 NOT LIKE 사용해서 환불 데이터 제외

+) WITH 절

❗INNER JOIN으로 테이블 결합
❗WHERE 절에 BETWEEN 사용해서 기간 설정