SQL 코테 준비 (9월 1주)(리트코드-2회)

8.31

1) Find Consistently Improving Employees

with base as (
    select employee_id,
           rating,
           review_date,
           row_number() over(partition by employee_id order by review_date desc) as rn,
           lag(rating, 1) over(partition by employee_id order by review_date) as prev1_rating,
           lag(rating, 2) over(partition by employee_id order by review_date) as prev2_rating,
           count(*) over(partition by employee_id) as review_count
    from performance_reviews
)
select b.employee_id,
       e.name,
       (b.rating - b.prev2_rating) as improvement_score
from base as b
join employees as e on b.employee_id = e.employee_id
where b.rating > b.prev1_rating and b.prev1_rating > b.prev2_rating
and b.review_count >= 3
and b.rn = 1
order by improvement_score desc, e.name asc

9.1

1) Find COVID Recovery Patients

with first_positive as (
    select patient_id,
           min(test_date) as first_pos_date
    from covid_tests
    where result = 'Positive'
    group by 1
),
first_negative as (
    select patient_id,
           min(test_date) as first_neg_date
    from covid_tests c
    join first_positive fp using(patient_id)
    where c.result = 'Negative'
    and c.test_date > fp.first_pos_date
    group by 1
)
select p.patient_id,
       p.patient_name,
       p.age, 
       datediff(fn.first_neg_date, fp.first_pos_date) as recovery_time
from patients p
join first_positive fp using(patient_id)
join first_negative fn using(patient_id)
order by 4,2

9.2

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

9.3

1) Find Overbooked Employees

with base as (
    select employee_id,
           year(meeting_date) as year,
           week(meeting_date,1) as week, 
           sum(duration_hours) as weekly_dh
    from meetings
    group by 1,2,3  
    having weekly_dh > 20
)
select e.employee_id,
       e.employee_name,
       e.department,
       count(b.weekly_dh) as meeting_heavy_weeks
from employees e
join base b using (employee_id)
group by 1
having meeting_heavy_weeks >= 2
order by 4 desc, 2

9.4

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