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

5.18

1) Find Category Recommendation Pairs

with base as (
    select distinct pp1.user_id,
                    pi1.category as category1,
                    pi2.category as category2
    from productpurchases pp1
    join productinfo pi1 on pp1.product_id = pi1.product_id
    join productpurchases pp2 on pp1.user_id = pp2.user_id
    join productinfo pi2 on pp2.product_id = pi2.product_id
    where pi1.category < pi2.category
)

select category1,
       category2,
       count(user_id) as customer_count
from base
group by category1, category2
having count(user_id) >= 3
order by customer_count desc, 1 asc, 2 asc;

5.19 ~ 5.21

1) Find Students with Study Spiral Pattern

=> 2사이클 이상 공부했는지에 대한 조건을 쿼리로 작성하지 못해 문제 풀이 완료하지 못함.

5.22

1) Find Zombie Sessions

select session_id, 
       user_id, 
       timestampdiff(minute,min(event_timestamp),max(event_timestamp)) as session_duration_minutes, 
       sum(if(event_type = 'scroll',1,0)) as scroll_count
from app_events
group by session_id
having session_duration_minutes > 30 
and scroll_count >= 5 
and sum(if(event_type = 'click',1,0))/scroll_count < 0.2 
and sum(if(event_type = 'purchase',1,0)) = 0
order by 4 desc, 1 asc