프로그래머스 SQL 고득점 kit 사이트 (JOIN)
주문량이 많은 아이스크림들 조회하기
Level 4
내 답안
WITH JULY_TOTAL_TABLE AS (
SELECT FLAVOR
, SUM(TOTAL_ORDER) AS TOTAL_ORDER
FROM JULY
GROUP BY FLAVOR
)
SELECT FH.FLAVOR AS FLAVOR
FROM FIRST_HALF AS FH
LEFT JOIN JULY_TOTAL_TABLE AS JT ON FH.FLAVOR = JT.FLAVOR
ORDER BY FH.TOTAL_ORDER + JT.TOTAL_ORDER DESC
LIMIT 3;특정 기간동안 대여 가능한 자동차들의 대여비용 구하기
Level 4
내 답안
SELECT CRCRH.CAR_ID AS 'CAR_ID'
, CRCC.CAR_TYPE AS 'CAR_TYPE'
, CEIL((CRCC.DAILY_FEE * (1 - CRCDP.DISCOUNT_RATE / 100)) * 30) AS 'FEE'
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS CRCRH
INNER JOIN CAR_RENTAL_COMPANY_CAR AS CRCC ON CRCRH.CAR_ID = CRCC.CAR_ID
INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS CRCDP ON CRCC.CAR_TYPE = CRCDP.CAR_TYPE
WHERE CRCC.CAR_TYPE IN ('세단', 'SUV')
AND CRCDP.DURATION_TYPE = '30일 이상'
GROUP BY CRCRH.CAR_ID
HAVING MAX(END_DATE) <= '2022-10-31 23:59:59'
AND FEE BETWEEN 500000 AND 1999999
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC;5월 식품들의 총매출 조회하기
Level 4
내 답안
SELECT FP.PRODUCT_ID
, FP.PRODUCT_NAME
, SUM(FP.PRICE * FO.AMOUNT) AS TOTAL_SALES
FROM FOOD_ORDER AS FO
INNER JOIN FOOD_PRODUCT AS FP ON FO.PRODUCT_ID = FP.PRODUCT_ID
WHERE DATE_FORMAT(FO.PRODUCE_DATE, '%Y-%m') = '2022-05'
GROUP BY PRODUCT_ID, PRODUCT_NAME
ORDER BY TOTAL_SALES DESC, FP.PRODUCT_ID ASC;조건에 맞는 도서와 저자 리스트 출력하기
Level 2
내 답안
SELECT B.BOOK_ID AS 'BOOK_ID'
, A.AUTHOR_NAME AS 'AUTHOR_NAME'
, DATE_FORMAT(B.PUBLISHED_DATE, '%Y-%m-%d') AS 'PUBLISHED_DATE'
FROM BOOK AS B
INNER JOIN AUTHOR AS A ON B.AUTHOR_ID = A.AUTHOR_ID
WHERE B.CATEGORY = '경제'
ORDER BY PUBLISHED_DATE ASC;그룹별 조건에 맞는 식당 목록 출력하기
Level 4
내 답안
WITH CNT_TABLE AS (
SELECT MP.MEMBER_NAME
, RR.REVIEW_TEXT
, RR.REVIEW_DATE
, COUNT(*) OVER(PARTITION BY RR.MEMBER_ID) AS CNT
FROM REST_REVIEW AS RR
INNER JOIN MEMBER_PROFILE AS MP ON RR.MEMBER_ID = MP.MEMBER_ID
ORDER BY CNT DESC
)
SELECT MEMBER_NAME
, REVIEW_TEXT
, DATE_FORMAT(REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM CNT_TABLE
WHERE CNT = (SELECT MAX(CNT) FROM CNT_TABLE)
ORDER BY REVIEW_DATE ASC, REVIEW_TEXT ASC;없어진 기록 찾기
Level 3
내 답안
SELECT AO.ANIMAL_ID AS 'ANIMAL_ID'
, AO.NAME AS 'NAME'
FROM ANIMAL_OUTS AS AO
LEFT JOIN ANIMAL_INS AS AI ON AO.ANIMAL_ID = AI.ANIMAL_ID
WHERE AI.ANIMAL_ID IS NULL
ORDER BY AO.ANIMAL_ID ASC, AO.NAME ASC;있었는데요 없었습니다
Level 3
내 답안
SELECT AI.ANIMAL_ID
, AI.NAME
FROM ANIMAL_INS AS AI
INNER JOIN ANIMAL_OUTS AS AO ON AI.ANIMAL_ID = AO.ANIMAL_ID
WHERE AO.DATETIME < AI.DATETIME
ORDER BY AI.DATETIME ASC;오랜 기간 보호한 동물(1)
Level 3
내 답안
SELECT AI.NAME AS 'NAME'
, AI.DATETIME AS 'DATETIME'
FROM ANIMAL_INS AS AI
LEFT JOIN ANIMAL_OUTS AS AO ON AI.ANIMAL_ID = AO.ANIMAL_ID
WHERE AO.ANIMAL_ID IS NULL
ORDER BY DATETIME ASC
LIMIT 3;보호소에서 중성화한 동물
Level 4
내 답안
SELECT AI.ANIMAL_ID
, AI.ANIMAL_TYPE
, AI.NAME
FROM ANIMAL_INS AS AI
INNER JOIN ANIMAL_OUTS AS AO ON AI.ANIMAL_ID = AO.ANIMAL_ID
WHERE AI.SEX_UPON_INTAKE LIKE '%Intact%'
AND (AO.SEX_UPON_OUTCOME LIKE '%Neutered%' OR AO.SEX_UPON_OUTCOME LIKE '%Spayed Female%')
ORDER BY AI.ANIMAL_ID ASC;상품 별 오프라인 매출 구하기
Level 2
내 답안
SELECT P.PRODUCT_CODE
, SUM(OS.SALES_AMOUNT * P.PRICE) AS SALES
FROM OFFLINE_SALE AS OS
INNER JOIN PRODUCT AS P ON OS.PRODUCT_ID = P.PRODUCT_ID
GROUP BY P.PRODUCT_CODE
ORDER BY SALES DESC, P.PRODUCT_CODE ASC;상품을 구매한 회원 비율 구하기
Level 5
내 답안
WITH NEW_TABLE AS (
SELECT OS.*
, (SELECT COUNT(DISTINCT USER_ID) AS CNT FROM USER_INFO WHERE YEAR(JOINED) = 2021) AS CNT
FROM ONLINE_SALE AS OS
INNER JOIN USER_INFO AS UI ON OS.USER_ID = UI.USER_ID
WHERE UI.JOINED BETWEEN '2021-01-01 00:00:00' AND '2021-12-31 23:59:59'
)
SELECT YEAR(SALES_DATE) AS 'YEAR'
, MONTH(SALES_DATE) AS 'MONTH'
, COUNT(DISTINCT USER_ID) AS 'PURCHASED_USERS'
, ROUND(COUNT(DISTINCT USER_ID) / CNT, 1) AS 'PUCHASED_RATIO'
FROM NEW_TABLE
GROUP BY MONTH(SALES_DATE)
ORDER BY YEAR ASC, MONTH ASC;FrontEnd 개발자 찾기
Level 4
내 답안
SELECT ID
, EMAIL
, FIRST_NAME
, LAST_NAME
FROM DEVELOPERS
WHERE SKILL_CODE & (SELECT SUM(CODE)
FROM SKILLCODES
WHERE CATEGORY = 'Front End') >= 1
ORDER BY ID ASC;이건 JOIN으로 안풀었다,,
Hi