카라멜마끼아또

개인 공부를 위한 공간입니다
상업적 용도로 활용되지 않습니다

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

풀이

1. author 테이블과 book 테이블 inner join

book_id, author_id, category, author_name, book.price 정보를 담은 테이블 출력

2. 1번 테이블과 book_sales 테이블 inner join 

3. 작가번호, 이름, 카테고리로 그룹화 후 price * sales 집계

select distinct a.author_id, a.author_name, category, sum(price*sales) as total_sales
from 
(
select book_id, author.author_id, category, author_name, book.price
from book inner join author
on book.author_id = author.author_id
) as a inner join book_sales as b
on a.book_id = b.book_id
where date_format(b.sales_date,'%Y-%m') = '2022-01'
group by author_id, author_name, category
order by a.author_id, category desc

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

풀이

조건을 살펴보면

1. 자동차 종류가 SUV, 세단

2. 2022-11-01~ 2022-11-30 사이의 대여 기록이 없고

3. 30일 대여 금액이 50만원 이상 200만원 미만

위 조건을 토대로

1. 2022-11-01~ 2022-11-30 사이에 대여 기록이 존재하는 car_id 목록을 가진 테이블 추출

2. 30일 이상 대여 시, 할인율 계산을 위해

car_rental_company_car 과 car_rental_company_discount_plan 테이블을 inner join

조인 조건은 car_type 이 일치하고 duration_type 이 '30일 이상'

3. where 조건절에서 1번 테이블의 목록에 있는 car_id 는 11-01 ~ 11-30 사이에 대여가 불가능하므로 not in 으로 제외

car_type 이 세단, SUV 이고, 대여 가격이 50만원 이상 200만원 미만일 때 

4. car_id, car_type, fee(대여 가격 계산 후 floor 로 소수점 제거) 출력

5. order by 로 fee, car_type, car_id 각각 내림차순, 오름차순, 내림차순으로 정렬

select car.car_id, car.car_type, floor(car.daily_fee * ((100-discount.discount_rate)/100) * 30) as fee
from car_rental_company_car as car inner join car_rental_company_discount_plan as discount
on car.car_type = discount.car_type and
discount.duration_type = '30일 이상'
where 
car_id not in
(
    select distinct car_id
    from car_rental_company_rental_history
    where end_date > '2022-11-01' and start_date < '2022-12-01'
    order by car_id
 )
and (car.car_type = 'SUV' or car.car_type = '세단')
and ((car.daily_fee * 30 * (1-(discount.discount_rate)/100)) between 500000 and 2000000)
order by car.daily_fee * 30 * (1-(discount.discount_rate)/100) desc, car.car_type asc, car.car_id desc

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

풀이

1. (price-price%10000) 으로 만원 단위로 그룹화

2. count 로 개수 출력

3. 그룹화 한 단위로 오름차순 정렬

select price- price%10000 as price_group,  count(*) as products
from product
group by price-price%10000
order by price-price%10000

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

풀이

1. car_rental_company_rental_history 테이블과 car_rental_company_car 테이블을 inner join

case 문으로 7일, 30일, 90일 이상의 값을 가진 duration 속성 출력 (7일 미만일 경우 null)

2. 1번에서 나온 테이블과 car_rental_company_discount_plan left outer join

duration 이 null 인 경우의 history 도 출력

3. case 문 한 번 더 써서 duration_type 이 null 인 경우는 daily_fee 와 date 를 곱하고

아닐 경우는 discount_rate 만큼의 할인이 적용된 가격 출력

select aa.history_id,
case
when duration_type is null then aa.daily_fee * date
else 
 floor((aa.daily_fee * date)-((aa.daily_fee * date) * discount_rate/100))
end as fee
from
(
    select car.car_id ,history.history_id, car.daily_fee as daily_fee, datediff(history.end_date, history.start_date)+1 as date,
    case
    when  datediff(history.end_date, history.start_date)+1 between 7 and 30 then '7일 이상'
    when datediff(history.end_date, history.start_date)+1 between 30 and 90 then '30일 이상'
    when datediff(history.end_date, history.start_date)+1 >= 90 then '90일 이상'
    end as duration
    from car_rental_company_car as car, car_rental_company_rental_history as history
    where car.car_id = history.car_id and car.car_type like '트럭'
) as aa
left outer join
car_rental_company_discount_plan as discount
on aa.duration = discount.duration_type and discount.car_type = '트럭'
order by fee desc, history.history_id desc

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

풀이

group by 로 간단하게 묶으면 될 것같지만, 조건에는 집계 결과가 0인 시간대도 출력해야 함

이를 위해 with recursive 재귀 쿼리를 사용

0 부터 1까지의 시간대별 데이터를 가진 hour 테이블을 animal_outs 테이블과 outer join

with recursive hourrange AS (
  select 0 AS hour
  union ALL
  select hour + 1
  from hourrange
  where hour < 23
)

select h.hour, count(animal_id)
from hourrange as h left outer join animal_outs as a
on h.hour = hour(a.datetime)
group by h.hour
order by h.hour;

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

풀이

매우 단순한 조인문제

두 테이블 조인 후, gender 가 null 이 아닌, user_id 를 distinct 로 중복 제거해서 카운트

select year(o.sales_date), month(o.sales_date) as month,u.gender as gender, count(distinct u.user_id) as users
from user_info as u inner join online_sale as o
on u.user_id = o.user_id and u.gender is not null
group by year(o.sales_date), month(o.sales_date), u.gender
order by year(o.sales_date), month(o.sales_date), u.gender

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

풀이

Milk, Yogurt 를 모두 담고있는 cart_id 를 출력하는 문제

1. Yogurt 를 담고 있는 카트 아이디 테이블 a

2. Milk 를 담고 있는 카트 아이디 테이블 b

3. a inner join b

4. cart_id 로 오름차순 정렬

select a.cart_id
from
(select cart_id, name
from cart_products
where name like 'Yogurt') as a,

(select cart_id, name
from cart_products
where name like 'Milk') as b

where a.cart_id = b.cart_id
order by a.cart_id

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

풀이

자동차 평균 대여 기간을 구하는 간단한 문제

1. 대여 기간 = datediff(end_date, start_date)+1

** 빌리는 당일도 대여 기간에 포함해야 하므로 1을 더해줘야 함

2. 대여기간의 평균 = avg(DATEDIFF(end_date, start_date)+1)

3. 대여기간의 평균에서 소수 둘 째 자리에서 반올림 = round(avg(DATEDIFF(end_date, start_date)+1), 1)

4. car_id 로 group by

5. 위 값 기준으로 내림차순, car_id 기준으로 내림차순

매우간단

select car_id, round(avg(DATEDIFF(end_date, start_date)+1), 1) as average_duration
from car_rental_company_rental_history
group by car_id
having round(avg(DATEDIFF(end_date, start_date)+1), 1) > 7
order by round(avg(DATEDIFF(end_date, start_date)+1), 1) desc, car_id desc;

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

풀이

위와 같은 테이블 구조에서

21년도 가입자의 연, 월 별 구매자 수와 구매자 비율을 구하는 문제

ONLINE_SALE 테이블의 user_id, sales_date

USER_INFO 테이블의 user_id, joined

1. sales_date 의 연도가 2021 인 USER_INFO 와 ONLINE_SALES 를 inner join

2. ONLINE_SALES 의 sales_date 연, 월로 group by

3. select 절에서 year, month 함수를 통해 sales_date 에서 각각 연, 월 출력

4.  21년 가입자 중, 22년 구매 내역이 있는 사용자의 조회를 위해 count 함수 사용

5. (4번 / USER_INFO 에서 가입년도가 2021인 유저의 수) 의 값을 구한 후 round 함수로 소수 첫번 째 자리까지 출력

여기서 주의해야 하는 점이 바로 중복되는 데이터

2021 년에 가입한 A 라는 사용자가

2022년 1월에 2번

2022년 2월에 1번 구매 내역이 있는 경우

월 별 구매 회원 수를 구해야 하기 때문에

1월, 2월 각각 1의 값 출력 필요

count 함수를 사용할 때 distinct 를 써서 같은 달에 여러 구매내역을 가진 유저의 중복을 제거

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