Day39

오랜 기간 보호한 동물

SELECT I.NAME, I.DATETIME
FROM ANIMAL_INS I
LEFT JOIN ANIMAL_OUTS O
ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE O.DATETIME IS NULL
ORDER BY I.DATETIME
LIMIT 3
;

보호소에서 중성화한 동물

SELECT I.ANIMAL_ID, I.ANIMAL_TYPE, I.NAME
FROM ANIMAL_INS I
JOIN ANIMAL_OUTS O
ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.SEX_UPON_INTAKE LIKE 'Intact%'
AND O.SEX_UPON_OUTCOME NOT LIKE 'Intact%'
ORDER BY I.ANIMAL_ID
;

상품별 오프라인 매출 구하기

SELECT P.PRODUCT_CODE, (SUM(O.SALES_AMOUNT) * P.PRICE) AS SALES
FROM OFFLINE_SALE O
JOIN PRODUCT P
ON O.PRODUCT_ID = P.PRODUCT_ID
GROUP BY O.PRODUCT_ID
ORDER BY 2 DESC, 1 ASC
;

상품을 구매한 회원 비율 구하기

SET @TOTAL = (
    SELECT COUNT(*) 
    FROM USER_INFO 
    WHERE YEAR(JOINED) = 2021
);

SELECT YEAR(O.SALES_DATE) AS YEAR, 
MONTH(O.SALES_DATE) AS MONTH, 
COUNT(DISTINCT O.USER_ID) AS PURCHASED_USERS, 
ROUND(COUNT(DISTINCT O.USER_ID) / @TOTAL, 1) AS PUCHASED_RATIO
FROM ONLINE_SALE O
LEFT JOIN USER_INFO U
ON O.USER_ID = U.USER_ID
WHERE YEAR(U.JOINED) = 2021
GROUP BY 1,2
ORDER BY 1,2
;