[MySQL] Programmers Lv.4, 5 전체 문제 풀이 (2023 업데이트!)

23년 업데이트된 Lv.4, 5 문제들까지 모두 풀어보았다. :)

 

Programmers의 모든 SQL 문제들을 풀어본 후기는!

확실히 최근 올라간 코딩테스트 난이도에 맞춰서 조금 더 수준높은 문제들이 생긴 것 같다.

 

그리고 예전에는 concat, substr, round, truncate, datediff와 같이 다양한 함수들을 이용하는 문제나

세 개의 테이블을 조인하는 문제, 그리고 두 개 이상의 컬럼으로 group by 하는 문제 등

문제의 다양성이 매우 부족해서 Programmers 만으로 SQL 코딩테스트를 대비하기에는 무리가 있었다.

 

이제는 새롭게 업데이트된 문제들까지 반복해서 풀어본다면, SQL 문제 풀이 대비에 충분하다고 느껴진다.

짱! 👍

 

기존 문제들의 풀이는 👉여기를 참고해주세요 !

 

식품분류별 가장 비싼 식품의 정보 조회하기

select category, price, product_name
from food_product
where category in ('과자', '국', '김치', '식용유')
and (category, price) in (select category, max(price)
                           from food_product
                           group by category)
order by price desc

 

5월 식품들의 총매출 조회하기

select p.product_id, p.product_name, sum(o.amount * p.price) as total_sales
from food_order as o join food_product as p
on o.product_id = p.product_id
where substr(produce_date, 1, 7) = '2022-05'
group by p.product_id
order by total_sales desc, p.product_id

 

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

select i.rest_id, rest_name, food_type, favorites, address, round(avg(review_score), 2) as score
from rest_info as i join rest_review as r
on i.rest_id = r.rest_id
where substr(address, 1, 2) = '서울'
group by i.rest_id
order by score desc, favorites desc

 

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

select member_name, review_text, date_format(review_date, '%Y-%m-%d')
from member_profile as p join rest_review as r
on p.member_id = r.member_id
where p.member_id = (select member_id
                    from rest_review
                    group by member_id
                    order by count(*) desc
                    limit 1)
order by review_date, review_text

 

년, 월, 성별 별 상품 구매 회원 수 구하기

select year(s.sales_date) as year, month(s.sales_date) as month, gender, count(distinct s.user_id) users
from user_info i join online_sale s on i.user_id = s.user_id
where gender is not null
group by year, month, gender
order by year asc, month asc, gender asc

 

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

select year(sales_date) as year, month(sales_date) as month, count(distinct u.user_id) as puchased_users, round((count(distinct u.user_id) / (select count(*) from user_info where year(joined) = 2021)), 1) as puchased_ratio
from (select * from user_info where year(joined) = 2021) as u
join online_sale as o
on u.user_id = o.user_id
group by year, month
order by year, month

 

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

select date_format(sales_date, "%Y-%m-%d") as sales_date, product_id, user_id, sales_amount
from online_sale 
where month(sales_date) = 3

union all

select date_format(sales_date, "%Y-%m-%d") as sales_date, product_id, null as user_id, sales_amount
from offline_sale
where month(sales_date) = 3

order by sales_date, product_id, user_id

 

취소되지 않은 진료 예약 조회하기

select x.apnt_no, p.pt_name, x.pt_no, x.mcdp_cd, x.dr_name, x.apnt_ymd
from patient as p
join (select a.apnt_ymd, a.apnt_no, a.pt_no, a.mcdp_cd, a.mddr_id, a.apnt_cncl_yn, a.apnt_cncl_ymd, d.dr_name
     from appointment as a
     join doctor as d
     on a.mddr_id = d.dr_id
     where date_format(apnt_ymd, '%Y-%m-%d') = '2022-04-13' 
      and apnt_cncl_yn = 'N') as x
on p.pt_no = x.pt_no
order by x.apnt_ymd

 

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

select f.flavor
from first_half as f join july as j
on f.flavor = j.flavor
group by f.flavor
order by sum(f.total_order) desc
limit 3

 

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

select b.author_id, a.author_name, b.category, sum(b.price * s.sales)
from book as b join author as a
on b.author_id = a.author_id join (select * from book_sales where substr(sales_date, 1, 7) = '2022-01') as s
on b.book_id = s.book_id
group by b.author_id, b.category
order by b.author_id, b.category desc

 

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

select history_id, truncate((datediff(end_date, start_date) + 1) * daily_fee
                         * (case
                            when (datediff(end_date, start_date) + 1) < 7 then 1
                            when (datediff(end_date, start_date) + 1) < 30 then 0.95
                            when (datediff(end_date, start_date) + 1) < 90 then 0.92
                            else 0.85
                            end), 0) as fee
from car_rental_company_car as c join car_rental_company_rental_history as h
on c.car_id = h.car_id
where c.car_type = '트럭'
order by fee desc, history_id desc

 

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

select distinct c.car_id, c.car_type, truncate(daily_fee * ((100 - p.discount_rate) * 0.01) * 30, 0) as fee
from car_rental_company_car as c
join car_rental_company_rental_history as h on c.car_id = h.car_id
join car_rental_company_discount_plan as p on c.car_type = p.car_type
where c.car_type in ('세단', 'SUV')
and c.car_id not in (select car_id
                     from car_rental_company_rental_history
                     where end_date >= '2022-11-01' 
                     and start_date <= '2022-11-30') and p.duration_type = '30일 이상'
having fee between 500000 and (2000000 - 1)
order by fee desc, c.car_type, c.car_id desc