[프로그래머스] LV2. 재구매가 일어난 상품과 회원 리스트 구하기 / 가격대 별 상품 개수 구하기 / 입양시각구하기(1) GROUP BY

1. 재구매가 일어난 상품과 회원 리스트 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/131536

SELECT user_id, product_id
FROM ONLINE_SALE
GROUP BY user_id, product_id
HAVING COUNT(*) > 1
ORDER BY USER_ID, PRODUCT_ID DESC

[OUT]

COUNT(*) 결과

SELECT USER_ID, COUNT(*)
FROM ONLINE_SALE
GROUP BY USER_ID

 

---

2. 가격대 별 상품 개수 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/131530

[내가 제출한 코드]

SELECT (CASE WHEN PRICE BETWEEN 0 AND 10000 THEN '0'
        WHEN PRICE BETWEEN 10000 AND 20000 THEN '10000'
        WHEN PRICE BETWEEN 20000 AND 30000 THEN '20000'
        WHEN PRICE BETWEEN 30000 AND 40000 THEN '30000'
        WHEN PRICE BETWEEN 40000 AND 50000 THEN '40000'
        WHEN PRICE BETWEEN 50000 AND 60000 THEN '50000'
        WHEN PRICE BETWEEN 60000 AND 70000 THEN '60000'
        WHEN PRICE BETWEEN 70000 AND 80000 THEN '70000'
        END) AS PRICE_GROUP, COUNT(*) AS PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP

-- 실행결과 동일하지만 정답 아님

 

[정답 코드]

SELECT TRUNCATE(PRICE, -4) AS PRICE_GROUP, COUNT(*) AS PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP

---

3. 입양 시각 구하기(1)

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

 

내가 제출한 코드

SELECT HOUR(DATETIME) AS 'HOUR', COUNT(HOUR(DATETIME)) AS 'COUNT'
FROM ANIMAL_OUTS
GROUP BY HOUR
HAVING HOUR BETWEEN 9 AND 19 -- 09:00~19:59 조건 설정
ORDER BY HOUR


# 2
SELECT HOUR(DATETIME) AS 'HOUR', COUNT(DATETIME) AS 'COUNT'
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) BETWEEN 9 AND 19 -- HAVING 말고 WHERE절도 가능
GROUP BY HOUR
ORDER BY HOUR

 

[OUT]