대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기 외 2건

1. 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

WITH FILTER AS ( -- 8~10월의 최소 1개 이상인 자동차 아이디만 가져오는 것
    SELECT CAR_ID
       , COUNT(*) AS FILTER
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE EXTRACT(MONTH FROM START_DATE) IN(8,9,10)
    GROUP BY CAR_ID
    ORDER BY CAR_ID DESC
)

SELECT EXTRACT(MONTH FROM START_DATE) AS MONTH
       , C.CAR_ID
       , COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY C INNER JOIN FILTER F
ON  C.CAR_ID = F.CAR_ID 
WHERE   F.FILTER>=5 
        AND EXTRACT(MONTH FROM START_DATE) IN (8,9,10)
GROUP BY EXTRACT(MONTH FROM START_DATE), C.CAR_ID
ORDER BY MONTH ASC, CAR_ID DESC

2. 우유와 요거트가 담긴 장바구니

WITH MOB AS(
    SELECT cart_id, name
    FROM CART_PRODUCTS
    WHERE NAME = 'Milk'
)

SELECT C.CART_ID
FROM MOB, CART_PRODUCTS C
WHERE C.NAME = 'Yogurt' AND MOB.CART_ID = C.CART_ID 
GROUP BY C.CART_ID
ORDER BY CART_ID ASC

• 굳이 With를 쓸 필요가 있었을까 싶긴 함…ㅋㅋㅋㅋ

3. 인기있는 아이스크림

SELECT FLAVOR
FROM FIRST_HALF
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID ASC

이번 주 월화수목만 가면 설연휴다 유후~~~ ^.<