Programmers SQL 문제 풀이 71 ~ 76

오프라인/온라인 판매 데이터 통합하기

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

SELECT date_format(sales_date, '%Y-%m-%d') AS sales_date, product_id, user_id, sales_amount
FROM ((
    SELECT *
    FROM online_sale
    WHERE sales_date LIKE '2022-03-%'
    )
    UNION
    (
    SELECT offline_sale_id, null, product_id, sales_amount, sales_date
    FROM offline_sale
    WHERE sales_date LIKE '2022-03-%'
    )) t1
ORDER BY 1, 2, 3

 

조건에 부합하는 중고거래 댓글 조회하기

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

SELECT t1.title, t1.board_id, t2.reply_id, t2.writer_id, t2.contents, DATE_FORMAT(t2.created_date, '%Y-%m-%d') AS created_date
FROM used_goods_board t1,
    used_goods_reply t2
WHERE 
    (t1.board_id = t2.board_id)
    AND
    (t1.created_date BETWEEN '2022-10-01' AND '2022-10-31')

ORDER BY t2.created_date, t1.title

 

입양 시각 구하기(2)

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

WITH RECURSIVE hour AS (
    SELECT 0 AS h 
    UNION ALL 
    SELECT h + 1
    from hour 
    where h < 23
)
SELECT t1.h AS HOUR, IFNULL(count, 0) AS COUNT
FROM hour t1
    LEFT JOIN (
        SELECT HOUR(datetime) AS hour, COUNT(animal_id) AS count
        FROM animal_outs
        GROUP BY hour
        ORDER BY hour
    ) t2
    ON t1.h = t2.hour

 

특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

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

SELECT car_id, car_type, fee
FROM (
    SELECT car_id, t1.car_type, ROUND((daily_fee * (1 - (discount_rate * 0.01))) * 30) AS FEE
    FROM car_rental_company_car t1
        LEFT JOIN (
            SELECT car_type, discount_rate
            FROM car_rental_company_discount_plan
            WHERE (car_type = '세단' OR car_type = 'SUV') AND duration_type = '30일 이상'
        ) t2
        ON t1.car_type = t2.car_type
    WHERE car_id NOT IN (
        SELECT car_id
        FROM car_rental_company_rental_history
        WHERE '2022-11-01' BETWEEN start_date AND end_date
            OR '2022-11-31' BETWEEN start_date AND end_date
        )
    ) t1
WHERE fee >= 500000 AND fee < 2000000  
ORDER BY 3 DESC, 2, 1 DESC

 

자동차 대여 기록 별 대여 금액 구하기

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

SELECT history_id, IFNULL(ROUND((daily_fee * (1 - (discount_rate * 0.01))) * t1.date), daily_fee * t1.date) AS FEE
FROM (
    SELECT history_id, daily_fee, (DATEDIFF(end_date, start_date) + 1) AS date 
    FROM car_rental_company_rental_history t1
        JOIN (
            SELECT car_id, daily_fee 
            FROM car_rental_company_car
            WHERE car_type = '트럭'
        ) t2
        ON t1.car_id = t2.car_id
    ) t1
    LEFT JOIN (
        SELECT REPLACE(duration_type, '일 이상', '') AS date, discount_rate
        FROM car_rental_company_discount_plan
        WHERE car_type = '트럭'
    ) t2
    ON t1.date >= t2.date
GROUP BY history_id
ORDER BY 2 DESC, 1 DESC

 

상품을 구매한 회원 비율 구하기

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

SELECT 
    year(sales_date), 
    month(sales_date), 
    count(distinct t2.user_id) AS PUCHASED_USERS, 
    round(
        count(distinct t2.user_id) / (
            SELECT count(distinct user_id)
            FROM user_info
            WHERE year(joined) = '2021'
        ), 1) AS PUCHASED_RATIO
FROM user_info t1
    LEFT JOIN online_sale t2
    ON t1.user_id = t2.user_id
WHERE year(joined) = '2021' AND sales_date is not null
GROUP BY 1, 2
ORDER BY 1, 2