6.15
1) 대장균의 크기에 따라 분류하기 2
select id,
case when size_rank = 1 then 'CRITICAL'
when size_rank = 2 then 'HIGH'
when size_rank = 3 then 'MEDIUM'
when size_rank = 4 then 'LOW'
end as colony_name
from (
select *,
ntile(4) over (order by size_of_colony desc) as size_rank
from ecoli_data
) as t
order by 1
2) 연도별 대장균 크기의 편차 구하기
select year(differentiation_date) as year,
(select max(size_of_colony)
from ecoli_data e1
where year = year(e1.differentiation_date)) - size_of_colony as year_dev,
id
from ecoli_data
order by 1,2
3) 조건에 맞는 개발자 찾기
SELECT id,
email,
first_name,
last_name
FROM developers
WHERE (skill_code & (SELECT code FROM skillcodes WHERE name = 'Python')) > 0
OR (skill_code & (SELECT code FROM skillcodes WHERE name = 'C#')) > 0
ORDER BY id ASC;
- 비트 AND 연산(&)은 두 비트열을 비교하여 두 비트 모두 1인 경우에만 1을 반환
6.16
1) FrontEnd 개발자 찾기
select distinct d.id,
d.email,
d.first_name,
d.last_name
from developers d
left join skillcodes s on s.code = d.skill_code & s.code
where s.category = 'Front End'
order by 1
2) 자동차 대여 기록 별 대여 금액 구하기
select history_id,
case when datediff(end_date, start_date) + 1 >= 90
then floor(daily_fee * (1 - (
select discount_rate
from car_rental_company_discount_plan
where car_type = '트럭' and duration_type = '90일 이상'
) / 100) * (datediff(end_date, start_date) + 1))
when datediff(end_date, start_date) + 1 >= 30
then floor(daily_fee * (1 - (
select discount_rate
from car_rental_company_discount_plan
where car_type = '트럭' and duration_type = '30일 이상') / 100
) * (datediff(end_date, start_date) + 1))
when datediff(end_date, start_date) + 1 >= 7
then floor(daily_fee * (1 - (
select discount_rate
from car_rental_company_discount_plan
where car_type = '트럭' and duration_type = '7일 이상') / 100
)* (datediff(end_date, start_date) + 1))
else daily_fee * (datediff(end_date, start_date) + 1)
end as fee
from car_rental_company_rental_history h
join car_rental_company_car c using(car_id)
where c.car_type = '트럭'
order by 2 desc, 1 desc;
6.17
1) 부모의 형질을 모두 가지는 대장균 찾기
select a.id,
a.genotype,
b.genotype as parent_genotype
from ecoli_data a
join ecoli_data b on a.parent_id = b.id
where a.genotype & b.genotype = b.genotype
order by 1
2) 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기
select c.car_id,
c.car_type,
round(c.daily_fee * 30 * (100-p.discount_rate)/100) as fee
from car_rental_company_car c
join car_rental_company_rental_history h on c.car_id = h.car_id
join car_rental_company_discount_plan p on c.car_type = p.car_type
where c.car_id not in (
select car_id
from car_rental_company_rental_history
where end_date >= '2022-11-01' and start_date <= '2022-12-01'
) and p.duration_type like '30%'
group by c.car_id
having c.car_type in ('세단', 'suv') and (fee >= 500000 and fee < 2000000)
order by 3 desc, 2, 1 desc;
6.18
1) 특정 형질을 가지는 대장균 찾기
select count(*) as count
from ecoli_data
where genotype & 2 = 0
and (genotype & 1 = 1 OR genotype & 4 = 4)
2) 상품을 구매한 회원 비율 구하기
6.19
1) 두 테이블 결합하기
select distinct athlete_id
from records r
join events e on r.event_id = e.id
where e.sport = 'Golf'
2) 레스토랑 웨이터의 팁 분석
select day,
time,
round(avg(tip),2) as avg_tip,
round(avg(size),2) as avg_size
from tips
group by day, time
order by 1,2
3) 일별 블로그 방문자 수 집계
select event_date_kst as dt,
count(distinct user_pseudo_id) as users
from ga
where event_date_kst between '2021-08-02' and '2021-08-09'
group by dt
order by 1
6.20
1) 우리 플랫폼에 정착한 판매자 2
select seller_id,
count(distinct order_id) as orders
from olist_order_items_dataset
where price >= 50
group by seller_id
having count(distinct order_id) >= 100
order by 2 desc
2) 레스토랑의 일일 매출
select day,
sum(total_bill) as revenue_daily
from tips
group by day
having sum(total_bill) >= 1000
order by 2 desc
3) 버뮤다 삼각지대에 들어가버린 택배
select date(order_delivered_carrier_date) as delivered_carrier_date,
count(distinct order_id) as orders
from olist_orders_dataset
where order_delivered_carrier_date like '2017-01%'
and (order_delivered_carrier_date is not null and order_delivered_customer_date is null)
group by 1
order by 1
