[240601] SQL 코딩테스트 문제풀이

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

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

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

 

WITH FIRST_Qu as (SELECT a.athlete_id, a.game_id, a.team_id
FROM records as a LEFT JOIN games as b
ON a.game_id = b.id
WHERE b.year >= 2000 AND a.medal is not null)

SELECT d.name
FROM (SELECT athlete_id
FROM FIRST_Qu as a LEFT JOIN teams as b
ON a.team_id = b.id
GROUP BY athlete_id
HAVING COUNT(DISTINCT team) >= 2) as c LEFT JOIN athletes as d 
ON c.athlete_id = d.id
ORDER BY d.name asc

 

---

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

• 주문횟수 => DISTINCT 해주어야 함

https://solvesql.com/problems/installment-month/

https://solvesql.com/problems/installment-month/

 

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. 지역별 주문의 특징

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

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

 

---

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

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

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

WITH SF_ORDER AS (SELECT order_purchase_timestamp as purchase_date, 
  CASE WHEN order_estimated_delivery_date > order_delivered_customer_date then 1 ELSE 0 END as 'success',
  CASE WHEN order_estimated_delivery_date <= order_delivered_customer_date then 1 ELSE 0 END as 'fail'
FROM olist_orders_dataset )

SELECT DATE(purchase_date) as 'purchase_date', SUM(success) as 'success', SUM(fail) as 'fail'
FROM SF_ORDER 
WHERE DATE(purchase_date) >= '2017-01-01' AND  DATE(purchase_date) <= '2017-01-31'
GROUP BY DATE(purchase_date)

 

---

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

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

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

SELECT DATE(a.order_purchase_timestamp) as 'dt',
  COUNT(DISTINCT(customer_id)) as 'pu',
  SUM(b.payment_value) as 'revenue_daily',
  ROUND((SUM(b.payment_value)/COUNT(DISTINCT(customer_id))),2) as 'arppu'

FROM olist_orders_dataset as a LEFT JOIN olist_order_payments_dataset as b
ON a.order_id = b.order_id

WHERE DATE(a.order_purchase_timestamp) >= '2018-01-01'
GROUP BY DATE(a.order_purchase_timestamp)
ORDER BY dt

 

---

6.  멘토링 짝궁 리스트

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

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

 

SELECT mentee.employee_id as 'mentee_id', 
  mentee.name as 'mentee_name',
  mentor.employee_id as 'mentor_id',
  mentor.name as 'mentor_name'
FROM (SELECT *
FROM employees
WHERE DATE(join_date) <= '2019-12-31') as mentor JOIN
(SELECT *
FROM employees
WHERE DATE(join_date) <= '2021-12-31' and DATE(join_date) >= '2021-09-31') as mentee
WHERE mentor.department != mentee.department
order by mentee_id asc, mentor_id asc

 

---

7. 작품이 없는 작가 찾기

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

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

 

SELECT d.artist_id, d.name
FROM (SELECT b.artwork_id, b.artist_id
FROM artworks as a LEFT JOIN artworks_artists as b
ON a.artwork_id = b.artwork_id 
) as c RIGHT JOIN artists as d 
ON c.artist_id = d.artist_id
WHERE d.death_year is not NULL and c.artwork_id is null

 

---

8. 연도별 대장균 크기 편차 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/299310

프로그래머스

 

WITH YEAR_MAX AS (SELECT YEAR(DIFFERENTIATION_DATE) as 'DATE_YEAR', 
                  MAX(SIZE_OF_COLONY) as 'MAX_YEAR'
FROM ECOLI_DATA
GROUP BY YEAR(DIFFERENTIATION_DATE))

SELECT a.DATE_YEAR as 'YEAR', b.MAX_YEAR - a.SIZE_OF_COLONY as 'YEAR_DEV', a.ID
FROM (SELECT *, YEAR(DIFFERENTIATION_DATE) as 'DATE_YEAR'
FROM ECOLI_DATA ) as a LEFT JOIN YEAR_MAX as b
ON a.DATE_YEAR = b.DATE_YEAR
ORDER BY a.DATE_YEAR asc, YEAR_DEV asc

 

---

9.  대장균의 크기에 따라 분류하기 2

https://school.programmers.co.kr/learn/courses/30/lessons/301649

프로그래머스

WITH TOTAL_COUNT AS (SELECT COUNT(*) as 'COUNT_TOTAL'
                    FROM ECOLI_DATA)


SELECT c.ID, 
    CASE WHEN c.percentile <= 0.25 then 'CRITICAL'
    WHEN  c.percentile <= 0.5 AND  c.percentile > 0.25 then 'HIGH'
    WHEN  c.percentile <= 0.75 AND  c.percentile > 0.5 then 'MEDIUM'
    ELSE 'LOW' END as 'COLONY_NAME'
FROM (SELECT ID, 
    (RANK() OVER (ORDER BY SIZE_OF_COLONY desc ) + 0.00) / (b.COUNT_TOTAL+0.00)
    as 'percentile'
FROM ECOLI_DATA as a JOIN TOTAL_COUNT as b) as c
ORDER BY c.ID

 

---

10. 업그레이드 할 수 없는 아이템 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/273712

프로그래머스

 

SELECT a.ITEM_ID, a.ITEM_NAME, a.RARITY
FROM ITEM_INFO as a LEFT JOIN (SELECT DISTINCT(PARENT_ITEM_ID) as 'disti'
    FROM ITEM_TREE) as b
ON a.ITEM_ID = b.disti
WHERE b.disti is null
ORDER BY a.ITEM_ID desc

 

---

11. Contest Leaderboard

https://www.hackerrank.com/challenges/contest-leaderboard/problem?isFullScreen=true

Contest Leaderboard | HackerRank

 

SELECT b.hacker_id, b.name, sum(a.max_score) as 'total_score'
FROM (SELECT hacker_id, challenge_id, max(score) as 'max_score'
FROM Submissions 
GROUP BY hacker_id, challenge_id ) as a JOIN Hackers as b
ON a.hacker_id = b.hacker_id
GROUP BY b.hacker_id, b.name 
HAVING total_score != 0
ORDER BY total_score desc, b.hacker_id asc

 

---

12. Placements

https://www.hackerrank.com/challenges/placements/problem?isFullScreen=true

Placements | HackerRank

 

SELECT d.Name
FROM Friends as a
    LEFT JOIN Packages as b ON a.ID = b.ID
    LEFT JOIN Packages as c ON a.Friend_ID = c.ID
    LEFT JOIN Students as d ON a.ID = d.ID
WHERE b.Salary < c.Salary
ORDER BY c.Salary

 

---

13.  Top Competitors

https://www.hackerrank.com/challenges/full-score/problem?isFullScreen=true

Top Competitors | HackerRank

SELECT s.hacker_id, h.name
FROM Submissions as s 
    LEFT JOIN Challenges as c ON s.challenge_id= c.challenge_id 
    LEFT JOIN Difficulty as d ON d.difficulty_level = c.difficulty_level
    LEFT JOIN Hackers as h ON s.hacker_id = h.hacker_id 
WHERE d.score = s.score
GROUP BY h.hacker_id, h.name
HAVING COUNT(h.name) > 1
ORDER BY COUNT(h.name) desc, h.hacker_id ASC