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

4.25

1) Find Students Who Improved

select *
from (
    select s1.student_id,
           s1.subject,
           max(case when s1.exam_date = (select min(s2.exam_date) from Scores s2 where s2.student_id = s1.student_id and s2.subject = s1.subject) then s1.score end) as first_score,
           max(case when s1.exam_date = (select max(s2.exam_date) from Scores s2 where s2.student_id = s1.student_id and s2.subject = s1.subject) then s1.score end) as latest_score
    from Scores as s1
    group by s1.student_id, s1.subject
) as t
where first_score < latest_score
order by student_id, subject

• case when 구문은 조건에 맞지 않을 때 null 값을 반환. 그렇기 때문에 MAX() 함수와 같은 집계함수를 사용해서 실제 값을 가져오도록 한다.
  - ex) 학생A/math/80/null => 80 의 값을 가져올 수 있도록

4.26

1) DNA Pattern Recognition

select sample_id,
       dna_sequence,
       species,
       case when dna_sequence like 'ATG%' then 1 else 0
       end as has_start,
       case when regexp_like(dna_sequence, '(TAA|TAG|TGA)$') then 1 else 0
       end as has_stop,
       case when dna_sequence like '%ATAT%' then 1 else 0
       end as has_atat,
       case when dna_sequence like '%GGG%' then 1 else 0
       end as has_ggg
from samples
order by sample_id

4.27

1) Analyze Subscription Conversion

select user_id,
       round(avg(case when activity_type = 'free_trial' then activity_duration end), 2) as trial_avg_duration,
       round(avg(case when activity_type = 'paid' then activity_duration end), 2) as paid_avg_duration
from UserActivity
where user_id in (select user_id from UserActivity where activity_type like '%paid%')
group by user_id
order by user_id

4.28

1) Seasonal Sales Analysis

with cte as (
    select 
        case 
            when month(a.sale_date) in (12, 1, 2) then 'Winter'
            when month(a.sale_date) in (3, 4, 5) then 'Spring'
            when month(a.sale_date) in (6, 7, 8) then 'Summer'
            else 'Fall'
        end as season,
        b.category,
        sum(a.quantity) as total_quantity,
        sum(a.quantity * a.price) as total_revenue
    from sales as a
    join products as b on a.product_id = b.product_id
    group by season, b.category
),
ranks as (
    select season,
           category,
           total_quantity,
           total_revenue,
           row_number() over (partition by season order by total_quantity desc, total_revenue desc) as rn
    from cte
)
select season,
       category,
       total_quantity,
       total_revenue
from ranks
where rn = 1
order by season asc

4.29

1) Find Product Recommendation Pairs

• 나의 풀이

with pairs as (
    select p1.user_id,
           p1.product_id as product1_id,
           p2.product_id as product2_id
    from ProductPurchases as p1
    join ProductPurchases as p2 on p1.user_id = p2.user_id 
    and p1.product_id < p2.product_id
)
select p.product1_id,
       p.product2_id,
       p1.category as product1_category,
       p2.category as product2_category,
       count(distinct p.user_id) as customer_count 
from pairs as p
join ProductInfo as p1 on p.product1_id = p1.product_id
join ProductInfo as p2 on p.product2_id = p2.product_id
group by p.product1_id, p.product2_id
having count(distinct p.user_id) >= 3
order by customer_count desc, product1_id asc, product2_id asc;

• 다른 사람의 풀이

SELECT
    P1.product_id AS product1_id,
    P2.product_id AS product2_id,
    PI1.category AS product1_category,
    PI2.category AS product2_category,
    COUNT(P1.user_id) AS customer_count

FROM ProductPurchases P1 
    INNER JOIN ProductPurchases P2 ON P1.user_id=P2.user_id AND P1.product_id<P2.product_id 
    LEFT JOIN ProductInfo PI1 ON P1.product_id=PI1.product_id
    LEFT JOIN ProductInfo PI2 ON P2.product_id=PI2.product_id

GROUP BY product1_id,product2_id 
HAVING COUNT(P1.user_id)>=3

ORDER BY customer_count DESC,product1_id,product2_id ;

=> 연달아 inner join과 left join으로 한 번에 처리한다면 처리 시간을 더 빠르게 할 수 있었다.

4.30

1) Find Consistently Improving Employees

with review 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 r.employee_id,
       e.name,
       (r.rating - r.prev2_rating) as improvement_score
from review as r
join employees as e on r.employee_id = e.employee_id
where r.rating > r.prev1_rating and r.prev1_rating > r.prev2_rating
and r.review_count >= 3
and r.rn = 1
order by improvement_score desc, e.name asc