주간 SQL Study 2차시 (SolveSQL, Programmers 문제풀이)

1️⃣ 중성화 여부 파악하기

Q1. https://programmers.co.kr/learn/courses/30/lessons/59409

코딩테스트 연습 - 중성화 여부 파악하기

 

SELECT ANIMAL_ID
     , NAME
     , CASE WHEN SEX_UPON_INTAKE IN ('Neutered Male','Spayed Female') THEN 'O' ELSE 'X' END AS '중성화'
FROM ANIMAL_INS

 

2️⃣ 없어진 기록 찾기

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

코딩테스트 연습 - 없어진 기록 찾기

 

SELECT ao.ANIMAL_ID
     , ao.NAME
FROM ANIMAL_OUTS ao
LEFT JOIN ANIMAL_INS ai ON ao.ANIMAL_ID = ai.ANIMAL_ID
WHERE ai.ANIMAL_ID is null

 

3️⃣ 입양 시각 구하기(1)

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

코딩테스트 연습 - 입양 시각 구하기(1)

 

SELECT hour(DATETIME) AS HOUR
     , count(distinct ANIMAL_ID) AS COUNT
FROM ANIMAL_OUTS
WHERE hour(DATETIME) BETWEEN 9 AND 20
GROUP BY HOUR
ORDER BY HOUR

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

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

solvesql

select date(order_purchase_timestamp) as purchase_date,
       count(case when order_delivered_customer_date < order_estimated_delivery_date then order_id end) as success,
       count(case when order_delivered_customer_date >= order_estimated_delivery_date then order_id end) as fail
from olist_orders_dataset
where order_purchase_timestamp between '2017-01-01' and '2017-01-31'
group by purchase_date
order by purchase_date

 

5️⃣ 최근 올림픽이 개최된 도시

Q5. https://solvesql.com/problems/olympic-cities/

solvesql

SELECT year, upper(left(city,3))
FROM games
WHERE year>=2000
ORDER BY year DESC