SQL 코테 준비 (5월 2주)(리트코드)

5.10

1) Find Churn Risk Customers

with base as (
    select user_id,
           plan_name as current_plan,
           monthly_amount as current_monthly_amount,
           cnt_downgrade
    from (
        select *, 
               max(event_date) over(partition by user_id) as last_date,
               max(case when event_type = 'downgrade' then 1 else 0 end) over(partition by user_id) as cnt_downgrade
        from subscription_events
    ) as t
    where event_date = last_date
)

select s.user_id,
       b.current_plan,
       b.current_monthly_amount,
       (select max(monthly_amount) from subscription_events) as max_historical_amount,
       datediff(max(event_date), min(event_date)) as days_as_subscriber
from subscription_events s
join base b using (user_id)
where b.cnt_downgrade > 0
group by s.user_id
having b.current_plan is not null
and b.current_monthly_amount / (select max(monthly_amount) from subscription_events) < 0.5
and datediff(max(event_date), min(event_date)) >= 60
order by 5 desc, 1

5.11

1) Find Emotionally Consistent Users

with base as (
    select user_id,
           reaction,
           count(*) as cnt,
           sum(count(distinct content_id)) over (partition by user_id) as total,
           rank() over (partition by user_id order by count(*) desc) as rk
    from reactions
    group by 1, 2
)

select user_id,
       reaction as dominant_reaction,
       round(cnt / total, 2) as reaction_ratio
from base
where rk = 1
and total >= 5
and cnt / total >= 0.6
order by 3 desc, 1;

5.12

1) Department Top Three Salaries

with concat_table as (
    select e.name as Employee,
           e.salary,
           d.name as Department
    from employee e
    inner join department d on e.departmentID = d.id
)

select Department,
       Employee,
       salary
from (
    select *,
           dense_rank () over (partition by Department order by salary desc) as dr
    from concat_table
) as t
where dr <= 3      

5.13

1) Trips and Users

select t.request_at as Day,
       round((count(case when t.status != 'completed' then 1 else null end)) / count(*) ,2) as 'Cancellation Rate'
from trips t
join users u1 on t.client_id = u1.users_id
join users u2 on t.driver_id = u2.users_id
where u1.banned = 'No' and u2.banned = 'No'
group by t.request_at
having t.request_at between '2013-10-01' and '2013-10-03'

• 별칭에 공백 포함하려면 ''로 감싸야 오류없이 코드가 실행된다.

5.14

1) Human Traffic of Stadium

with base as(
    select *, 
           lag(id, 2) over(order by id) as lag2_id,
           lag(id, 1) over(order by id) as lag_id,
           lead(id, 1) over(order by id) as lead_id,
           lead(id, 2) over(order by id) as lead2_id
    from Stadium
    where people >= 100
)
select id, 
       visit_date, 
       people
from base
where (id+1=lead_id and lead_id+1=lead2_id)
or (id-1=lag_id and id+1=lead_id)
or (id-1=lag_id and id-2=lag2_id)

• Solutions에서 참고한 다른 풀이 방법

with t1 as (
    select id, people, visit_date,
    id - rank() over (order by id) as rnk
    from Stadium
    where people >= 100
)
select id, visit_date, people 
from t1
where rnk in (select rnk from t1 group by rnk having count(*) >= 3)

=> id에서 rank을 빼준 것을 rnk로 이름 붙이고, rnk를 기준으로 group by를 해주어 같은 값이(count 했을 때) 3개 이상인 값을 불러온다.

5.15

1) Find Invalid IP Addresses

select ip, 
       count(*) as invalid_count
from logs
where length(ip) - length(replace(ip, '.', '')) != 3
or substring_index(ip, '.', 1) regexp '^0[0-9]'
or substring_index(substring_index(ip, '.', 2), '.', -1) regexp '^0[0-9]'
or substring_index(substring_index(ip, '.', 3), '.', -1) regexp '^0[0-9]'
or substring_index(ip, '.', -1) regexp '^0[0-9]'
or cast(substring_index(ip, '.', 1) as unsigned) > 255
or cast(substring_index(substring_index(ip, '.', 2), '.', -1) as unsigned) > 255
or cast(substring_index(substring_index(ip, '.', 3), '.', -1) as unsigned) > 255
or cast(substring_index(ip, '.', -1) as unsigned) > 255
group by ip
order by 2 desc, 1 desc;