프로그래머스 SQL 고득점 kit 사이트 (GROUP BY)
자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기
Level 3
내 답안
SELECT CAR_ID
, CASE
WHEN CAR_ID IN (SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE '2022-10-16 00:00:00' BETWEEN START_DATE AND END_DATE) THEN '대여중'
ELSE '대여 가능'
END AS 'AVAILABILITY '
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC;저자 별 카테고리 별 매출액 집계하기
Level 4
내 답안
WITH SUM_SALES_TABLE AS (
SELECT BOOK_ID
, SUM(SALES) AS SUM_SALES
FROM BOOK_SALES
WHERE SALES_DATE BETWEEN '2022-01-01 00:00:00' AND '2022-01-31 23:59:59'
GROUP BY BOOK_ID
ORDER BY BOOK_ID ASC
), NEW_TABLE AS (
SELECT A.AUTHOR_ID
, A.AUTHOR_NAME
, B.CATEGORY
, B.PRICE * SST.SUM_SALES AS TOTAL_SALES
FROM SUM_SALES_TABLE AS SST
INNER JOIN BOOK AS B ON SST.BOOK_ID = B.BOOK_ID
INNER JOIN AUTHOR AS A ON B.AUTHOR_ID = A.AUTHOR_ID
)
SELECT AUTHOR_ID
, AUTHOR_NAME
, CATEGORY
, SUM(TOTAL_SALES) AS TOTAL_SALES
FROM NEW_TABLE
GROUP BY AUTHOR_NAME, CATEGORY
ORDER BY AUTHOR_ID ASC, CATEGORY DESC;성분으로 구분한 아이스크림 총 주문량
Level 2
내 답안
SELECT II.INGREDIENT_TYPE
, SUM(TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF AS FH
INNER JOIN ICECREAM_INFO AS II ON II.FLAVOR = FH.FLAVOR
GROUP BY II.INGREDIENT_TYPE
ORDER BY TOTAL_ORDER ASC;식품분류별 가장 비싼 식품의 정보 조회하기
Level 4
내 답안
WITH RNK_TABLE AS (
SELECT *
, DENSE_RANK() OVER(PARTITION BY CATEGORY ORDER BY PRICE DESC) AS RNK
FROM FOOD_PRODUCT
WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
)
SELECT CATEGORY
, PRICE AS MAX_PRICE
, PRODUCT_NAME
FROM RNK_TABLE
WHERE RNK = 1
ORDER BY PRICE DESC;진료과별 총 예약 횟수 출력하기
Level 2
내 답안
SELECT MCDP_CD AS '진료과코드'
, COUNT(*) AS '5월예약건수'
FROM APPOINTMENT
WHERE APNT_YMD BETWEEN '2022-05-01 00:00:00' AND '2022-05-31 23:59:59'
GROUP BY MCDP_CD
ORDER BY COUNT(*) ASC, MCDP_CD ASC;즐겨찾기가 가장 많은 식당 정보 출력하기
Level 3
내 답안
WITH NEW_TABLE AS (
SELECT *
, MAX(FAVORITES) OVER(PARTITION BY FOOD_TYPE) AS 'BEST'
FROM REST_INFO
)
SELECT FOOD_TYPE
, REST_ID
, REST_NAME
, FAVORITES
FROM NEW_TABLE
WHERE FAVORITES = BEST
ORDER BY FOOD_TYPE DESC;대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기
Level 3
내 답안
WITH CAR_ID_TABLE AS (
SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE >= '2022-08-01 00:00:00'
AND START_DATE <= '2022-10-31 23:59:59'
GROUP BY CAR_ID
HAVING COUNT(*) >= 5
)
SELECT MONTH(START_DATE) AS MONTH
, CAR_ID
, COUNT(HISTORY_ID) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (SELECT * FROM CAR_ID_TABLE)
AND START_DATE BETWEEN '2022-08-01 00:00:00' AND '2022-10-31 23:59:59'
GROUP BY CAR_ID, MONTH(START_DATE)
ORDER BY MONTH ASC, CAR_ID DESC;자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기
Level 2
내 답안
SELECT CAR_TYPE
, COUNT(CAR_ID) AS CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%통풍시트%'
OR OPTIONS LIKE '%열선시트%'
OR OPTIONS LIKE '%가죽시트%'
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE ASC;조건에 맞는 사용자와 총 거래금액 조회하기
Level 3
내 답안
WITH NEW_USER_TABLE AS (
SELECT WRITER_ID
, SUM(PRICE) AS SUM_PRICE
FROM USED_GOODS_BOARD
WHERE STATUS = 'DONE'
GROUP BY WRITER_ID
HAVING SUM_PRICE >= 700000
)
SELECT UGU.USER_ID AS USER_ID
, UGU.NICKNAME AS NICKNAME
, NUT.SUM_PRICE AS TOTAL_SALES
FROM USED_GOODS_USER AS UGU
INNER JOIN NEW_USER_TABLE AS NUT ON UGU.USER_ID = NUT.WRITER_ID
ORDER BY TOTAL_SALES ASC;카테고리 별 도서 판매량 집계하기
Level 3
내 답안
SELECT B.CATEGORY AS CATEGORY
, SUM(BS.SALES) AS TOTAL_SALES
FROM BOOK_SALES AS BS
INNER JOIN BOOK AS B ON BS.BOOK_ID = B.BOOK_ID
WHERE SALES_DATE BETWEEN '2022-01-01 00:00:00' AND '2022-01-31 23:59:59'
GROUP BY B.CATEGORY
ORDER BY B.CATEGORY ASC;고양이와 개는 몇 마리 있을까
Level 2
내 답안
SELECT ANIMAL_TYPE
, COUNT(ANIMAL_ID) AS 'count'
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE ASC;동명 동물 수 찾기
Level 2
내 답안
SELECT NAME
, COUNT(*) AS 'COUNT'
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME
HAVING COUNT(*) >= 2
ORDER BY NAME ASC;년, 월, 성별 별 상품 구매 회원 수 구하기
Level 4
내 답안
WITH NEW_TABLE AS (
SELECT OS.USER_ID
, YEAR(OS.SALES_DATE) AS YEAR
, MONTH(OS.SALES_DATE) AS MONTH
, UI.GENDER
FROM ONLINE_SALE AS OS
INNER JOIN USER_INFO AS UI ON OS.USER_ID = UI.USER_ID
GROUP BY USER_ID, MONTH(OS.SALES_DATE)
)
SELECT YEAR
, MONTH
, GENDER
, COUNT(USER_ID) AS USERS
FROM NEW_TABLE
GROUP BY YEAR, MONTH, GENDER
HAVING GENDER IS NOT NULL
ORDER BY YEAR ASC, MONTH ASC, GENDER ASC;입양 시각 구하기(1)
Level 2
내 답안
SELECT HOUR(DATETIME) AS 'HOUR'
, COUNT(ANIMAL_ID) AS 'COUNT'
FROM ANIMAL_OUTS
GROUP BY HOUR(DATETIME)
HAVING HOUR >= 9
AND HOUR < 20
ORDER BY HOUR ASC;입양 시각 구하기(2)
Level 4
내 답안
WITH RECURSIVE HOUR_TABLE AS (
SELECT 0 AS HOUR
UNION ALL
SELECT HOUR+1 FROM HOUR_TABLE
WHERE HOUR < 23
)
SELECT HT.HOUR AS 'HOUR'
, COUNT(ANIMAL_ID) AS 'COUNT'
FROM HOUR_TABLE AS HT
LEFT JOIN ANIMAL_OUTS AS AO ON HT.HOUR = HOUR(AO.DATETIME)
GROUP BY HT.HOUR
ORDER BY HT.HOUR ASC;가격대 별 상품 개수 구하기
Level 2
내 답안
SELECT FLOOR(PRICE / 10000) * 10000 AS PRICE_GROUP
, COUNT(*) AS 'PRODUCTS'
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP ASC;언어별 개발자 분류하기
Level 4
내 답안
WITH FRONT_END AS (
SELECT SUM(CODE) AS 'CODE'
FROM SKILLCODES
GROUP BY CATEGORY
HAVING CATEGORY = 'Front End'
), FINAL_TABLE AS (
SELECT CASE
WHEN (SKILL_CODE & (SELECT CODE FROM SKILLCODES WHERE NAME = 'Python')) != 0 AND (SKILL_CODE & (SELECT CODE FROM FRONT_END)) THEN 'A'
WHEN (SKILL_CODE & (SELECT CODE FROM SKILLCODES WHERE NAME = 'C#')) != 0 THEN 'B'
WHEN (SKILL_CODE & (SELECT CODE FROM FRONT_END)) !=0 THEN 'C'
ELSE NULL
END AS 'GRADE'
, ID
, EMAIL
FROM DEVELOPERS
ORDER BY GRADE ASC, ID ASC
)
SELECT *
FROM FINAL_TABLE
WHERE GRADE IS NOT NULL;조건에 맞는 사원 정보 조회하기
Level 2
내 답안
SELECT SUM(HG.SCORE) OVER(PARTITION BY EMP_NO) AS 'SCORE'
, HE.EMP_NO
, HE.EMP_NAME
, HE.POSITION
, HE.EMAIL
FROM HR_EMPLOYEES AS HE
INNER JOIN HR_GRADE AS HG ON HE.EMP_NO = HG.EMP_NO
ORDER BY SCORE DESC
LIMIT 1;연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기
Level 4
내 답안
WITH CAL_TABLE AS (
SELECT HE.EMP_NO
, HE.EMP_NAME
, CASE
WHEN AVG(HG.SCORE) >= 96 THEN 'S'
WHEN AVG(HG.SCORE) >= 90 THEN 'A'
WHEN AVG(HG.SCORE) >= 80 THEN 'B'
ELSE 'C'
END AS 'GRADE'
, HE.SAL
FROM HR_EMPLOYEES AS HE
LEFT JOIN HR_GRADE AS HG ON HE.EMP_NO = HG.EMP_NO
GROUP BY HE.EMP_NO
ORDER BY HE.EMP_NO
)
SELECT EMP_NO
, EMP_NAME
, GRADE
, CASE
WHEN GRADE = 'S' THEN SAL * 0.2
WHEN GRADE = 'A' THEN SAL * 0.15
WHEN GRADE = 'B' THEN SAL * 0.1
ELSE SAL * 0
END AS 'BONUS'
FROM CAL_TABLE
ORDER BY EMP_NO ASC;부서별 평균 연봉 조회하기
Level 3
내 답안
SELECT HE.DEPT_ID
, HD.DEPT_NAME_EN
, ROUND(AVG(SAL)) AS 'AVG_SAL'
FROM HR_EMPLOYEES AS HE
INNER JOIN HR_DEPARTMENT AS HD ON HE.DEPT_ID = HD.DEPT_ID
GROUP BY HE.DEPT_ID, HD.DEPT_NAME_EN
ORDER BY AVG_SAL DESC;노선별 평균 역 사이 거리 조회하기
Level 2
내 답안
SELECT ROUTE
, CONCAT(ROUND(SUM(D_BETWEEN_DIST), 1), 'km') AS 'TOTAL_DISTANCE'
, CONCAT(ROUND(AVG(D_BETWEEN_DIST), 2), 'km') AS 'AVERAGE_DISTANCE'
FROM SUBWAY_DISTANCE
GROUP BY ROUTE
ORDER BY SUM(D_BETWEEN_DIST) DESC;물고기 종류 별 잡은 수 구하기
Level 2
내 답안
SELECT COUNT(ID) AS 'FISH_COUNT'
, FNI.FISH_NAME AS 'FISH_NAME'
FROM FISH_INFO AS FI
INNER JOIN FISH_NAME_INFO AS FNI ON FI.FISH_TYPE = FNI.FISH_TYPE
GROUP BY FNI.FISH_NAME
ORDER BY FISH_COUNT DESC;월별 잡은 물고기 수 구하기
Level 2
내 답안
SELECT COUNT(ID) AS 'FISH_COUNT'
, MONTH(TIME) AS 'MONTH'
FROM FISH_INFO
GROUP BY MONTH(TIME)
HAVING FISH_COUNT > 0
ORDER BY MONTH ASC;특정 조건을 만족하는 물고기별 수와 최대 길이 구하기
Level 3
내 답안
WITH NEW_TABLE AS (
SELECT ID
, FISH_TYPE
, CASE
WHEN LENGTH IS NULL THEN 10
ELSE LENGTH
END AS NEW_LENGTH
, TIME
FROM FISH_INFO
)
SELECT COUNT(ID) AS FISH_COUNT
, MAX(NEW_LENGTH) AS MAX_LENGTH
, FISH_TYPE
FROM NEW_TABLE
GROUP BY FISH_TYPE
HAVING AVG(NEW_LENGTH) >= 33
ORDER BY FISH_TYPE ASC;
Hi