[코테 연습] Leetcode 코딩테스트 연습 MySQL Med.

Leetcode 코딩테스트 연습 MySQL Med.

---

3497. Analyze Subscription Conversion

: A subscription service wants to analyze user behavior patterns. The company offers a 7-day free trial, after which users can subscribe to a paid plan or cancel. Find users who converted from free trial to paid subscription. Calculate each user's average daily activity duration during their free trial period. Calculate each user's average daily activity duration during their paid subscription period. Return the result table ordered by user_id in ascending order.

 

풀이 1 : CASE WHEN ELSE END 구문 

- 조건에 맞지 않은 행 먼저 필터링 후 집계함수 적용 

- 집계함수는 자동으로 null 제거하고 계산함을 이용

  이때 null이 아닌 0으로 하면 평균 계산에서 분모가 전체 데이터 수가 되므로 옳지 않음

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

 

풀이 2 : DECODE 

- 위와 동일하게 조건에 맞지 않은 행 먼저 필터링 후 집계함수 적용 

- decode(대상, 값1, 리턴1, 값2, 리턴2, ..., 그외 리턴): 대상이 값n과 일치하면 리턴n, 없으면 그외 리턴 출력

- `case when 대상 = 값 then 리턴 else 그외 리턴`

  이 구조는 `decode(대상, 값, 리턴, 그외 리턴)`과 같은 동작 

select user_id,
    round(avg(decode(activity_type, 'free_trial', activity_duration , null)),2) as trial_avg_duration,
    round(avg(decode(activity_type, 'paid', activity_duration, null)),2) as paid_avg_duration
from UserActivity
where user_id in (select user_id from UserActivity
                    where activity_type = 'paid')
group by user_id
order by user_id

 

풀이 2

- 집계함수 적용 후 조건에 맞지 않아 null로 출력되는 행을 제거

select * from
    (select user_id, 
    round(avg(case activity_type when 'free_trial' then 1.0*activity_duration else null end), 2) as trial_avg_duration,
    round(avg(case activity_type when 'paid' then 1.0*activity_duration else null end), 2) as paid_avg_duration
    from UserActivity 
    group by user_id) T
where paid_avg_duration is not null

 

 

 

 

3220. Odd and Even Transactions

: Write a solution to find the sum of amounts for odd and even transactions for each day. If there are no odd or even transactions for a specific date, display as 0. Return the result table ordered by transaction_date in ascending order.

 

풀이 1 : CASE WHEN ELSE END 구문 

select to_char(transaction_date, 'YYYY-MM-DD') as transaction_date,
    sum(case when mod(amount, 2) = 1 then amount else 0 end) as odd_sum,
    sum(case when mod(amount, 2) = 0 then amount else 0 end) as even_sum
from transactions
group by transaction_date
order by transaction_date

 

풀이 2 : DECODE 

select to_char(transaction_date, 'YYYY-MM-DD') as transaction_date,
    sum(decode(mod(amount, 2), 1, amount, 0)) as odd_sum,
    sum(decode(mod(amount, 2), 0, amount, 0)) as even_sum
from transactions
group by transaction_date
order by transaction_date

 

 

 

 

 

626. Exchange Seats

: Write a solution to swap the seat id of every two consecutive students. If the number of students is odd, the id of the last student is not swapped.

 

풀이 

SELECT LAG(col) OVER (ORDER BY col2)

• ORDER BY절에서 col2를 기준으로 정렬한 후
• LAG : 이전 행의 col1 값 가져오기
• LEAD : 이후 행의 col1 값 가져오기

- coalesce(대상1, 대상2, ...) : 처음으로 null이 아닌 값 리턴

- NVL(대상, 치환값) : 대상이 null이면 치환값 리턴

# coalesce 사용
select id, 
    case when mod(id, 2) = 0 then lag(student) over (order by id) 
         else coalesce(lead(student) over (order by id), student) end as student
from Seat
order by id


# nvl 사용
select id, nvl(case when mod(id, 2) = 0 then lag(student) over (order by id)
                ELSE lead(student) over (order by id) END, 
                student
                ) as student
from Seat
order by id

 

select id, nullif(case when mod(id, 2) = 0 then lag(student) over (order by id)
                ELSE lead(student) over (order by id) END, 
                student
                ) as student
from Seat
order by id

 

 

 

 

 

1204. Last Person to Fit in the Bus

: There is a queue of people waiting to board a bus. However, the bus has a weight limit of 1000 kilograms, so there may be some people who cannot board. Write a solution to find the person_name of the last person that can fit on the bus without exceeding the weight limit.

 

풀이

SUM(col1) OVER (ORDER BY col2)

• ORDER BY절에서 col2를 기준으로 정렬한 후
• 위쪽부터 차례대로 col1의 누적합 계산

ROWNUM : Oracle에서 출력 행 수 제한

- 현재 출력된 데이터 상태를 기준으로 행 번호 부여

- 절대적인 행 번호가 아닌 가상의 번호이므로 특정 행을 지정할 수 없음

  '<' 연산만 가능, '='나 '>' 조건 전달 불가능

select person_name
from (select person_name, turn, sum(weight) over(order by turn) as cul_weight
    from Queue
    order by turn desc) Q
where cul_weight <= 1000 and rownum < 2

 

 

 

 

1045. Customers Who Bought All Products

 

: Write a solution to report the customer ids from the Customer table that bought all the products in the Product table.

 

풀이 1

- HAVING절 안에 SELECT 서브쿼리 사용하기

select customer_id
from Customer
group by customer_id
having count(distinct product_key) = (select count(distinct product_key) from Product)

 

풀이 2

- NOT EXISTS 사용

- (전체 product_id - 특정 고객이 구매한 product_id) 이 결과가 NOT EXISTS이면

  특정 고객이 전체 product_id를 다 구매한 것이므로

select distinct customer_id
from Customer C1
where not exists(
    select count(distinct product_key) from Product
    minus
    select count(distinct product_key) from Customer C2 where C2.customer_id = C1.customer_id
)

 

 

풀이 3

- NOT EXISTS 사용

- product_id NOT IN (특정 고객이 구매한 product_id)를 만족하는 product_id가 NOT EXISTS이면

  특정 고객이 전체 product_id를 다 구매한 것이므로

select distinct customer_id
from Customer C1
where not exists(
    select 1 from Product
    where product_key not in (select distinct product_key
                             from Customer C2 where C2.customer_id = C1.customer_id)
)