🚫[실무]solvesql SQLite 연습문제 난이도3

■ 출처

- 코딩테스트 : SQLite / 연습문제 / 난이도3

https://solvesql.com/problems/?page=1

https://solvesql.com/problems/?page=1

 

 

■ 연습문제

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

SELECT
  a.name
FROM
  records AS r
  JOIN athletes AS a ON r.athlete_id = a.id
  JOIN games AS g ON r.game_id = g.id
  JOIN teams AS t ON r.team_id = t.id
WHERE
  r.medal IS NOT NULL
  AND g.year >= 2000
GROUP BY
  r.athlete_id
HAVING
  COUNT(DISTINCT t.team) >= 2
ORDER BY  
  a.name ASC

※ 4개 테이블을 JOIN하여 최종 1개 테이블을 만들어야하는 상황이라 'records' 테이블을 중심으로 결합하여 구축

   (구축 시, 복수 국적을 고려하여 'team'변수에 조건 적용)

 

2. 할부는 몇 개월로 해드릴까요

SELECT
  payment_installments,
  COUNT(DISTINCT order_id) AS order_count, 
  MIN(payment_value) AS min_value,
  MAX(payment_value) AS max_value,
  AVG(payment_value) AS avg_value   
FROM
  olist_order_payments_dataset 
WHERE
  payment_type = 'credit_card'
GROUP BY
  payment_installments

 

3. 지역별 주문의 특징

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

※ CASE WHEN '조건' THEN '조건 결과' END 함수를 활용하여 조건에 맞고 중복된 정보를 제거한 결과를 조회

 

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

SELECT
  DATE(order_purchase_timestamp) AS purchase_date,
  COUNT(CASE WHEN date(order_delivered_customer_date) < date(order_estimated_delivery_date) then order_id end) as success,
  COUNT(CASE WHEN date(order_delivered_customer_date) >= date(order_estimated_delivery_date) then order_id end) as fail
FROM
  olist_orders_dataset 
WHERE 
  order_delivered_customer_date IS NOT NULL
  AND order_estimated_delivery_date IS NOT NULL
  AND DATE(order_purchase_timestamp) LIKE '2017-01%'
GROUP BY
  DATE(order_purchase_timestamp)
ORDER BY
  purchase_date ASC

 

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

ARPPU(Average Revenue Per Paying User) : 결제 유저 1인당 평균 결제액

SELECT
  DATE(a_table.order_purchase_timestamp) AS dt,
  COUNT(DISTINCT a_table.order_id) AS  pu,
  SUM(b_table.payment_value) AS revenue_daily,
  ROUND(SUM(b_table.payment_value) / COUNT(DISTINCT a_table.order_id), 2) AS arppu 
FROM
  olist_orders_dataset AS a_table
  JOIN olist_order_payments_dataset AS b_table
  ON a_table.order_id = b_table.order_id
WHERE
  a_table.order_purchase_timestamp > '2018-01-01'
GROUP BY
  DATE(a_table.order_purchase_timestamp)

 

6. 멘토링 짝꿍 리스트

CROSS JOIN : 상호조인이라 불리며, A테이블의 모든 행과 B테이블의 모든 행을 조인하는 기능

SELECT
  A.employee_id AS mentee_id, A.name AS mentee_name,
  B.employee_id AS mentor_id, B.name AS mentor_name
FROM
  employees AS A
  CROSS JOIN employees AS B
WHERE
  A.join_date BETWEEN '2021-09-01' and '2021-12-31'
  AND B.join_date <= '2019.12.31'
  AND A.department != B.department
ORDER BY
  mentee_id, mentor_id

 

 

7. 작품이 없는 작가 찾기

SELECT
  A_Table.artist_id, A_Table.name 
FROM 
  artists AS A_Table
  LEFT JOIN artworks_artists AS B_Table
  ON A_Table.artist_id = B_Table.artist_id
WHERE
  A_Table.death_year IS NOT NULL 
  AND B_Table.artist_id IS NULL

※ JOIN(INNER JOIN)과 LEFT JOIN(LEFT OUTER JOIN)의 차이점을 확인