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

소프·2022년 3월 26일
0

SQL Study

목록 보기
5/6
post-thumbnail
  1. 복수 국적 메달 수상한 선수 찾기

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

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
  1. 있었는데요 없었습니다

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
  1. 헤비 유저가 소유한 장소

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
  1. 상위 N개 레코드

Q4. https://leetcode.com/problems/department-highest-salary/

SELECT name
FROM animal_ins
ORDER BY datetime asc
limit 1
  1. 버뮤다 삼각지대에 들어가버린 택배

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

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)
profile
세상을 긍정적으로 변화시키는 Business Analyst가 되기위해 노력중입니다.

0개의 댓글