✅ 코드 SELECT YEAR(OS.SALES_DATE) AS YEAR , MONTH(OS.SALES_DATE) AS MONTH , COUNT(DISTINCT OS.USER_ID) AS PURCHASED_USERS , ROUND(COUNT(DISTINCT OS.USER_ID) / (SELECT COUNT(DISTINCT USER_ID) FROM USER_INFO WHERE YEAR(JOINED) = 2021), 1) AS PURCHASED_RATIO FROM USER_INFO AS UI INNER JOIN ONLINE_SALE AS OS ON UI.USER_ID = OS.USER_ID WHERE YEAR(UI.JOINED) = 2021 GROUP BY YEAR , MONTH ORDER BY YEAR , ..
✅ 코드 WITH RENTAL_HISTORY AS ( SELECT * , CASE WHEN DATEDIFF(END_DATE, START_DATE) + 1 < 7 THEN NULL WHEN DATEDIFF(END_DATE, START_DATE) + 1 < 30 THEN '7일 이상' WHEN DATEDIFF(END_DATE, START_DATE) + 1 < 90 THEN '30일 이상' ELSE '90일 이상' END AS DURATION_TYPE FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY ), TRUCKS AS ( SELECT * FROM CAR_RENTAL_COMPANY_CAR WHERE CAR_TYPE = '트럭' ) SELECT H.HISTORY_ID AS HISTORY_..
✅ 코드 WITH T AS ( SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE , PRODUCT_ID , USER_ID , SALES_AMOUNT FROM ONLINE_SALE WHERE SALES_DATE BETWEEN '2022-03-01 00:00:00' AND '2022-03-31 23:59:59' UNION ALL SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE , PRODUCT_ID , NULL , SALES_AMOUNT FROM OFFLINE_SALE WHERE SALES_DATE BETWEEN '2022-03-01 00:00:00' AND '2022-03-31 23:59:59' ) S..
✅ 코드 SELECT CART_ID FROM CART_PRODUCTS WHERE NAME IN ('Milk', 'Yogurt') GROUP BY CART_ID HAVING COUNT(DISTINCT NAME) = 2 ORDER BY CART_ID GROUP BY와 HAVING문을 사용한 풀이입니다. 핵심은 HAVING문에서 DISTINCT 키워드를 사용했다는 점입니다. 🔎 참고 WITH MILK_YOGURT_CARTS AS ( SELECT CART_ID , CASE WHEN SUM(IF(NAME IN ('Milk', 'Yogurt'), 1, 0)) = 2 THEN 'O' ELSE 'X' END AS TARGET FROM CART_PRODUCTS GROUP BY CART_ID ) SELECT CART_ID..
✅ 코드 SELECT P.PRODUCT_ID AS PRODUCT_ID , P.PRODUCT_NAME AS PRODUCT_NAME , SUM(P.PRICE * O.AMOUNT) AS SALES FROM FOOD_PRODUCT AS P INNER JOIN FOOD_ORDER AS O ON P.PRODUCT_ID = O.PRODUCT_ID WHERE O.PRODUCE_DATE BETWEEN '2022-05-01 00:00:00' AND '2022-05-31 23:59:59' GROUP BY PRODUCT_ID ORDER BY SALES DESC , PRODUCT_ID INNER JOIN → 5월 생산 식품목록 필터링(WHERE) → GROUP BY 순으로 풀이가 가능합니다.
✅ 코드 SELECT MONTH(START_DATE) AS MONTH , CAR_ID , COUNT(HISTORY_ID) AS RECORDS FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE CAR_ID IN (SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE MONTH(START_DATE) IN (8, 9, 10) GROUP BY CAR_ID HAVING COUNT(HISTORY_ID) >= 5) AND MONTH(START_DATE) IN (8, 9, 10) GROUP BY MONTH, CAR_ID HAVING COUNT(HISTORY_ID) >= 1 ORDER BY MONTH , CAR_ID DESC; 우선적으로 ..
✅ 코드 SELECT CAR_ID , CASE WHEN SUM(IF('2022-10-16' BETWEEN START_DATE AND END_DATE, 1, 0)) = 0 THEN '대여 가능' ELSE '대여중' END AS AVAILABILITY FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY GROUP BY CAR_ID ORDER BY CAR_ID DESC; 그룹화를 수행한 뒤에 CASE문이 수행됩니다.
✅ 코드 SELECT C.CAR_ID AS CAR_ID , C.CAR_TYPE AS CAR_TYPE , FLOOR(C.DAILY_FEE * 30 * (1 - P.DISCOUNT_RATE/100)) AS FEE FROM CAR_RENTAL_COMPANY_CAR AS C INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS P ON C.CAR_TYPE = P.CAR_TYPE AND P.DURATION_TYPE = '30일 이상' AND C.CAR_TYPE IN ('세단', 'SUV') LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H ON C.CAR_ID = H.CAR_ID AND H.END_DATE >= '2022-11-01' AND H...
