5.2
1) Odd and Even Transactions
with base as (
select transaction_date,
if(amount%2 = 1 , amount, 0 ) as'odd_sum',
if(amount%2 = 0 , amount, 0 ) as'even_sum'
from transactions
)
select transaction_date, sum(odd_sum) as 'odd_sum', sum(even_sum) as 'even_sum'
from base
group by transaction_date
order by transaction_date
5.3
1) Find COVID Recovery Patients
with first_positive as (
select patient_id,
min(test_date) as first_positive_date
from covid_tests
where result = 'positive'
group by patient_id
),
first_negative as (
select ct.patient_id,
min(ct.test_date) as first_negative_date
from covid_tests as ct
join first_positive as fp on ct.patient_id = fp.patient_id
where ct.result = 'negative' and ct.test_date > fp.first_positive_date
group by ct.patient_id
)
select p.patient_id,
p.patient_name,
p.age,
datediff(fn.first_negative_date, fp.first_positive_date) as recovery_time
from first_positive as fp
join first_negative as fn on fp.patient_id = fn.patient_id
join patients as p on p.patient_id = fp.patient_id
order by recovery_time asc, p.patient_name asc
5.4
1) Find Drivers with Improved Fuel Efficiency
with base as (
select driver_id,
case when trip_date between '2023-01-01' and '2023-06-30' then distance_km / fuel_consumed
else null
end as first_half_fuel,
case when trip_date between '2023-07-01' and '2023-12-31' then distance_km / fuel_consumed
else null
end as second_half_fuel
from trips
),
stats as (
select driver_id,
avg(first_half_fuel) as first_half_avg,
avg(second_half_fuel) as second_half_avg
from base
group by driver_id
having first_half_avg is not null
and second_half_avg is not null
)
select s.driver_id,
d.driver_name,
round(s.first_half_avg, 2) as first_half_avg,
round(s.second_half_avg, 2) as second_half_avg,
round(s.second_half_avg - s.first_half_avg, 2) as efficiency_improvement
from stats s
join drivers d on s.driver_id = d.driver_id
where first_half_avg < second_half_avg
order by efficiency_improvement desc, d.driver_name asc
- base 서브쿼리에서 case문에서 평균값 처리했다면 더 빠른 처리 시간 기록할 수 있었을 것.
5.5
1) Find Overbooked Employees
with meeting_hours as (
select employee_id,
year(meeting_date) as year,
week(meeting_date, 1) as week,
sum(duration_hours) hours
from meetings
group by employee_id, year, week
),
weeks_count as (
select employee_id,
employee_name,
department,
count(1) as meeting_heavy_weeks
from meeting_hours
join employees using (employee_id)
where hours >= 20
group by employee_id
)
select employee_id,
employee_name,
department,
meeting_heavy_weeks
from weeks_count
where meeting_heavy_weeks >= 2
order by meeting_heavy_weeks desc, employee_name
5.6
1) Find Stores with Inventory Imbalance
with price as (
select store_id,
max(price) as highest_price,
min(price) as lowest_price
from inventory
group by store_id
having count(distinct product_name) >= 3
),
inventory_cte as (
select p.store_id,
s.store_name,
s.location,
i.price,
i.product_name,
i.quantity
from inventory i
inner join price p on (i.price = p.highest_price or i.price = p.lowest_price)
inner join stores s on s.store_id = i.store_id
)
select ic1.store_id,
ic1.store_name,
ic1.location,
ic1.product_name as most_exp_product,
ic2.product_name as cheapest_product,
round((ic2.quantity/ic1.quantity),2) as imbalance_ratio
from inventory_cte ic1
inner join inventory_cte ic2 on ic1.store_id = ic2.store_id and ic1.price > ic2.price and ic1.quantity < ic2.quantity
order by imbalance_ratio desc, ic1.store_name asc
5.7
1) Find Books with Polarized Opinions
with base as (
select book_id,
count(*) as sessions,
count(case when session_rating >= 4 or session_rating <= 2 then 1 end) as polarization,
max(session_rating) as max_rating,
min(session_rating) as min_rating
from reading_sessions
group by book_id
having max(session_rating) >= 4 and min(session_rating) <= 2 and count(*) >= 5
)
select ba.book_id,
b.title,
b.author,
b.genre,
b.pages,
ba.max_rating - ba.min_rating as rating_spread,
round((ba.polarization / ba.sessions),2) as polarization_score
from base ba
join books b using (book_id)
where round((ba.polarization / ba.sessions),2) >= 0.6
order by 7 desc, 2 desc
- with문 사용하지 않고 한 번에 작성하는 방법(위 방법은 런타임 많이 소요됨)
select
reading_sessions.book_id,
title,
author,
genre,
pages,
max(session_rating) - min(session_rating) as 'rating_spread',
round(sum(session_rating >= 4 or session_rating <= 2)/count(distinct session_id),2) as 'polarization_score'
from
reading_sessions
join books on reading_sessions.book_id = books.book_id
group by
book_id
having count(distinct session_id) >= 5 and sum(session_rating >= 4) >= 1 and sum(session_rating <= 2) >= 1 and polarization_score >= 0.6
order by polarization_score desc, title desc
5.8
1) Find Loyal Customers
select customer_id
from customer_transactions
group by customer_id
having count(*) >= 3
and datediff(max(transaction_date), min(transaction_date)) >= 30
and sum(transaction_type = 'refund') / count(*) < 0.2
order by 1 asc
5.9
1) Find Golden Hour Customers
select customer_id,
count(*) as total_orders,
round(sum(case when time(order_timestamp) between '11:00:00' and '14:00:00'
or time(order_timestamp) between '18:00:00' and '21:00:00' then 1 else 0 end) / count(*) * 100) as peak_hour_percentage,
round(avg(order_rating),2) as average_rating
from restaurant_orders
group by customer_id
having count(*) >= 3
and (sum(case when time(order_timestamp) between '11:00:00' and '14:00:00'
or time(order_timestamp) between '18:00:00' and '21:00:00' then 1 else 0 end) / count(*)) >= 0.6
and (sum(case when order_rating is not null then 1 else 0 end) / count(*)) >= 0.5
and round(avg(order_rating),2) >= 4.0
order by 4 desc, 1 desc
