60 년, 월, 성별 별 상품 구매 회원 수 구하기
SELECT YEAR(SALES_DATE) YEAR, MONTH(SALES_DATE), GENDER, COUNT(DISTINCT U.USER_ID) USERS
FROM USER_INFO U JOIN ONLINE_SALE O ON U.USER_ID=O.USER_ID
WHERE GENDER IS NOT NULL
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3
61 서울에 위치한 식당 목록 출력하기
SELECT I.REST_ID, REST_NAME, FOOD_TYPE, FAVORITES, ADDRESS, ROUND(AVG(REVIEW_SCORE), 2) SCORE
FROM REST_INFO I JOIN REST_REVIEW R ON I.REST_ID=R.REST_ID
WHERE ADDRESS LIKE '서울%'
GROUP BY 1
ORDER BY SCORE DESC, FAVORITES DESC
62 자동차 대여 기록에서 장기/단기 대여 구분하기
SELECT HISTORY_ID, CAR_ID, DATE_FORMAT(START_DATE, '%Y-%m-%d') START_DATE, DATE_FORMAT(END_DATE, '%Y-%m-%d') END_DATE,
CASE WHEN DATEDIFF(END_DATE, START_DATE) >= 29 THEN '장기 대여'
ELSE '단기 대여'
END RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE LIKE '2022-09%'
ORDER BY HISTORY_ID DESC
63 자동차 평균 대여 기간구하기
SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE, START_DATE)+1), 1) AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVERAGE_DURATION >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC
64 헤비 유저가 소유한 장소 2021 Dev-Matching: 웹 백엔드 개발자(상반기)
SELECT *
FROM PLACES
WHERE HOST_ID
IN
(
SELECT HOST_ID
FROM PLACES
GROUP BY HOST_ID
HAVING COUNT(*) >= 2
)
ORDER BY ID