주간 SQL Study 5차시 (SolveSQL, programmers 문제풀이)

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

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

solvesql

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

 

2️⃣ 있었는데요 없었습니다

Q2. https://programmers.co.kr/learn/courses/30/lessons/59043

코딩테스트 연습 - 있었는데요 없었습니다

 

SELECT ai.animal_id
     , ai.name
FROM animal_ins ai LEFT JOIN animal_outs ao ON ai.animal_id = ao.animal_id
WHERE ai.datetime > ao.datetime
ORDER BY ai.datetime

 

3️⃣ 헤비 유저가 소유한 장소

Q3. https://programmers.co.kr/learn/courses/30/lessons/77487

코딩테스트 연습 - 헤비 유저가 소유한 장소

 

SELECT p1.id
     , p1.name
     , p1.host_id
FROM PLACES p1 INNER JOIN (
    SELECT host_id, count(*) cnt
    FROM places
    GROUP BY host_id
) p2 ON p1.host_id = p2.host_id
WHERE p2.cnt > 1
ORDER BY p1.id

4️⃣ 상위 N개 레코드

Q4. https://programmers.co.kr/learn/courses/30/lessons/59405

코딩테스트 연습 - 상위 n개 레코드

SELECT name
FROM animal_ins
ORDER BY datetime asc
limit 1

 

5️⃣ 버뮤다 삼각지대에 들어가버린 택배

Q5. https://solvesql.com/problems/shipment-in-bermuda/

solvesql

SELECT date(order_delivered_carrier_date) as delivered_carrier_date
     , count(*) as orders
FROM olist_orders_dataset
WHERE order_delivered_customer_date is null 
  AND date(order_delivered_carrier_date) between '2017-01-01' and '2017-01-31' 
  AND order_delivered_carrier_date IS NOT NULL
GROUP BY date(order_delivered_carrier_date)
ORDER BY date(order_delivered_carrier_date)