(Programmers) SQL 풀이 (8)

✏️ Programmers MySQL 정답률 69% 전체 풀이

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

SELECT USER_ID,PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID # USER_ID 기준 한번, PRODUCT_ID 기준 한번 그룹핑
HAVING COUNT(online_sale_id) > 1
ORDER BY USER_ID ASC, PRODUCT_ID DESC;

GROUP BY USER_ID, PRODUCT_ID 결과

2️⃣ 헤비 유저가 소유한 장소 (https://school.programmers.co.kr/learn/courses/30/lessons/77487)

# 방법 (1) : left join 사용
SELECT P.ID, P.NAME, P.HOST_ID
FROM PLACES AS P
LEFT JOIN ( # SUB 테이블을 통해 등록한 장소가 2개 이상인 유저 목록 확인
    SELECT HOST_ID
    FROM PLACES
    GROUP BY HOST_ID
    HAVING COUNT(ID) >1
) AS SP
ON P.HOST_ID = SP.HOST_ID # LEFT join 통해 선별
WHERE SP.HOST_ID IS NOT NULL
ORDER BY P.ID;

# 방법 (2)-1 : inner join 사용
SELECT P.ID, P.NAME, P.HOST_ID
FROM PLACES AS P
INNER JOIN (
    SELECT HOST_ID
    FROM PLACES
    GROUP BY HOST_ID
    HAVING COUNT(ID) >1
) AS SP
ON P.HOST_ID = SP.HOST_ID
ORDER BY P.ID;

# 방법 (2)-2 : inner join 을 좀 더 깔끔하게
SELECT P.ID, P.NAME, P.HOST_ID
FROM PLACES AS P
JOIN (
    SELECT HOST_ID, COUNT(ID) AS 'COUNT'
    FROM PLACES
    GROUP BY HOST_ID
) AS SP
ON P.HOST_ID = SP.HOST_ID
WHERE SP.COUNT > 1
ORDER BY P.ID;

# 방법 (3) : IN 사용
SELECT *
FROM PLACES
WHERE HOST_ID IN (
    SELECT HOST_ID 
    FROM PLACES
    GROUP BY HOST_ID
    HAVING COUNT(ID) > 1
)
ORDER BY ID;

3️⃣ 가격대 별 상품 개수 구하기 (https://school.programmers.co.kr/learn/courses/30/lessons/131530)

SELECT (
    CASE
    WHEN PRICE < 10000 THEN 0
    ELSE TRUNCATE(PRICE, -4) # 내림을 통해 범위 지정
    END
) AS PRICE_GROUP , COUNT(PRODUCT_ID)
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP;

# 반올림(ROUND) 과 내림(TRUNCATE)

ROUND([숫자], [반올림할 자릿수]) # 숫자를 반올림할 자릿수+1 자릿수에서 반올림함, 자릿수 명시는 선택
# 예시
SELECT ROUND(3456.1234567) FROM table_name # (결과) : 3456 
SELECT ROUND(3456.1234567 ,-1) FROM table_name # (결과) : 3460

TRUNCATE([숫자], [버릴 자릿수]) # 숫자를 버릴 자릿수 아래로 버림, 자릿수 명시는 필수
#예시
SELECT TRUNCATE(3456.1234567 ,1) FROM table_name; # (결과) : 3456.1
SELECT TRUNCATE(3456.1234567 ,-2) FROM table_name; # (결과) : 3400