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
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
Q3. https://programmers.co.kr/learn/courses/30/lessons/59412
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
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
SELECT year, upper(left(city,3))
FROM games
WHERE year>=2000
ORDER BY year DESC