Programmers SQL 문제 풀이 61 ~ 70

서울에 위치한 식당 목록 출력하기

https://school.programmers.co.kr/learn/courses/30/lessons/131118

-- 1. 서울에 위치한 식당
-- 2. 식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수 조회
-- 3. 평균 점수는 소수점 세 번째 자리에서 반올림
-- 4. 평균 점수 기준 내림차순, 즐겨찾기수 기준 내림차순 정렬
-- ----------------------------------------------------------------
-- 1. rest_review 테이블에서 식당 ID, 점수를 추출한다.
-- 2. rest_info와 rest_review에서 추출한 데이터를 조인한다.
-- 3. address에서 '서울'과 일치하는 주소만 필터링한다.
-- 4. 식당 ID를 기준으로 그룹화 한다.
-- 4. 식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 점수(ROUND(AVG))를 추출한다.
-- 5. 점수를 기준 내림차순, 즐겨찾기수 기준 내림차순 한다.

SELECT t1.rest_id, rest_name, food_type, favorites, address, ROUND(AVG(review_score), 2) AS score
FROM rest_info t1
    JOIN (
        SELECT rest_id, review_score
        FROM rest_review
    ) t2
    ON t1.rest_id = t2.rest_id
WHERE address LIKE '서울%'
GROUP BY rest_id
ORDER BY score DESC, favorites DESC

 

자동차 대여 기록에서 장기 / 단기 대여 구분하기

https://school.programmers.co.kr/learn/courses/30/lessons/151138

SELECT history_id, car_id, DATE_FORMAT(start_date, '%Y-%m-%d'), DATE_FORMAT(end_date, '%Y-%m-%d'), 
    CASE
        WHEN DATEDIFF(end_date, start_date) >= 29 THEN '장기 대여' ELSE '단기 대여'
    END AS rent_type
FROM car_rental_company_rental_history
WHERE start_date BETWEEN '2022-09-01' AND '2022-09-30'
ORDER BY history_id DESC

 

자동차 평균 대여 기간 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/157342

-- 1. 평균 대여 기간이 7일 이상인 자동차 v
-- 2. 자동차 ID, 평균 대여 기간 출력 v
-- 3. 평균 대여 기간의 컬럼명 : average_duration v
-- 4. 평균 대여 기간을 소수점 두 번째 자리에서 반올림 v
-- 5. 평균 대여 기간 기준 내림차순, 자동차 ID 기준 내림차순 v
-- --------------------------------------------------
-- 당일 대여/반납에 대한 날짜도 고려해야 함(당일 대여/반납의 경우 0일이 되기 때문에 날짜에 +1 필요)
SELECT car_id, ROUND(AVG(ABS(DATEDIFF(start_date, end_date)) + 1), 1) AS average_duration
FROM car_rental_company_rental_history
GROUP BY car_id
HAVING average_duration >= 7
ORDER BY average_duration DESC, car_id DESC

 

헤비 유저가 소유한 장소 2021 Dev-Matching: 웹 백엔드 개발자(상반기)

https://school.programmers.co.kr/learn/courses/30/lessons/77487

-- 1. 공간을 둘 이상 등록한 사람
-- 2. 등록한 공간의 정보를 아이디순으로 조회
-- -------------------------------------
SELECT *
FROM places
WHERE host_id IN (
    SELECT host_id
    FROM places
    GROUP BY host_id
    HAVING COUNT(host_id) > 1
)
ORDER BY id

우유와 요거트가 담긴 장바구니 Summer/Winter Coding(2019)

https://school.programmers.co.kr/learn/courses/30/lessons/62284

-- 1. 우유와 요거트를 동시에 구입한 장바구니
-- 2. 아이디를 조회
-- 3. 아이디순 정렬
-- ------------------------------------
SELECT cart_id
FROM (
    SELECT DISTINCT cart_id, name
    FROM cart_products
    WHERE name IN ('Milk', 'Yogurt')
) t1
GROUP BY 1
HAVING COUNT(cart_id) > 1
ORDER BY 1

 

조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/164671

-- 1. 조회수가 가장 높은 중고 거래 게시물의
-- 2. 첨부파일 경로 조회
-- 3. file id 기준 내림차순 정렬
-- 4. /home/grep/src/ + board_id + file_id + file_name + file_ext로 출력
-- -----------------------------------------------------------
SELECT CONCAT('/home/grep/src/', board_id, '/',  file_ID, file_name, file_ext) AS file_path
FROM used_goods_file
WHERE board_id = (
    SELECT board_id
    FROM used_goods_board
    ORDER BY views DESC limit 1
)
ORDER BY file_id DESC

 

주문량이 많은 아이스크림들 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/133027

-- 1. 7월 아이스크림 총 주문량 + 상반기 아이스크림 총 주문량
-- 2. 더한 값이 큰 순서대로 상위 3개의 맛 출력
-- ---------------------------------------------------
with t1 as (
    SELECT flavor, sum(total_order) AS total_order
    FROM july
    GROUP BY flavor
)
SELECT t1.flavor
FROM t1
    JOIN first_half t2
    ON t1.flavor = t2.flavor
ORDER BY t1.total_order + t2.total_order DESC limit 3

 

저자 별 카테고리 별 매출액 집계하기

https://school.programmers.co.kr/learn/courses/30/lessons/144856

-- 1. 2022년 1월의 도서 판매 데이터 v
-- 2. 저자 별, 카테고리 별 매출액(판매량 * 판매가)
-- 3. 저자 ID, 저자명, 카테고리, 매출액 조회
-- 4. 저자 ID 기준 오름차순, 카테고리 기준 내림차순
-- -------------------------------------------
with t1 as (
    SELECT book_id, sales
    FROM book_sales
    WHERE sales_date LIKE '2022-01%'
), t2 as (
    SELECT author_id, category, sales * price AS total_sales
    FROM t1
        JOIN book t2
        ON t1.book_id = t2.book_id
)
SELECT t2.author_id, author_name, category, sum(total_sales) AS sales
FROM t2
    JOIN author t3
    ON t2.author_id = t3.author_id
GROUP BY t2.author_id, category
ORDER BY 1, 3 DESC

대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/151139

SELECT month(start_date), car_id, count(car_id) AS records
FROM car_rental_company_rental_history
WHERE car_id IN (
    SELECT car_id
    FROM car_rental_company_rental_history
    WHERE start_date BETWEEN '2022-08-01' AND '2022-10-31'
    GROUP BY 1
    HAVING count(car_id) > 4
) 
AND month(start_date) IN (8, 9, 10)
GROUP BY 1, 2
ORDER BY 1, 2 DESC

그룹별 조건에 맞는 식당 목록 출력하기

https://school.programmers.co.kr/learn/courses/30/lessons/131124

SELECT member_name, review_text, DATE_FORMAT(review_date, '%Y-%m-%d')
FROM rest_review t1
    JOIN member_profile t2
    ON t1.member_id = t2.member_id
WHERE t1.member_id IN (
    SELECT member_id -- 리뷰를 가장 많이 작성한 회원 ID
    FROM rest_review
    GROUP BY member_id
    HAVING count(member_id) = (
        SELECT max(count) -- 회원 별 가장 많이 작성된 리뷰의 개수
        FROM (
            SELECT count(member_id) AS count
            FROM rest_review
            GROUP BY member_id
        ) t1
    )
)
ORDER BY 3, 2