19.sql

-- [ 프로그래머스 ] 가격대 별 상품 개수 구하기

SELECT
    FLOOR(PRICE / 10000) * 10000 AS PRICE_GROUP,
    COUNT(PRODUCT_ID) AS PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP ASC;


-- 아래와 같이 공통 테이블 표현식(Common Table Expression, CTE)을 활용해서 문제를 풀 수도 있다.

WITH RECURSIVE PRICES (START_PRICE, END_PRICE) AS (
    SELECT
        0 AS START_PRICE,
        10000 AS END_PRICE
    UNION ALL
    SELECT
        START_PRICE + 10000 AS START_PRICE,
        END_PRICE + 10000 AS END_PRICE
    FROM PRICES
    WHERE START_PRICE < (SELECT MAX(PRICE) FROM PRODUCT)
)

SELECT
    PRICES.START_PRICE AS PRICE_GROUP,
    COUNT(PRODUCT.PRODUCT_ID) AS PRODUCTS
FROM PRICES
JOIN PRODUCT
ON PRODUCT.PRICE BETWEEN PRICES.START_PRICE AND PRICES.END_PRICE - 1
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP ASC;