[리트코드] SQL - #601, #262, #1393, #1613

601. Human Traffic of Stadium

hard 난이도 문제입니다.

Q. 3개 이상의 연속적인 id와 100명 이상의 사람이 방문한 id를 찾아라. 날짜 기준, 오름차순으로 정렬

풀이

with문을 사용해서 id에서 id 기준 row_number를 뺀 값, 즉 id_diff를 계산합니다. 이때, 100명 이상의 사람이 방문할 것을 조건으로 설정합니다. with 문을 사용해야하기 때문에 mysql 대신 ms sql server를 사용했습니다.

id_diff가 변하지 않고 동일하다면 연속되는 숫자라고 볼 수 있습니다. 따라서 view를 생성한 뒤에는 id_diff 기준 count가 3개인 행을 필터링합니다.

with temp as (
    select *, id - row_number() over(order by id) as id_diff
    from stadium where people >= 100
)

select id, visit_date, people
from temp
where id_diff in (select id_diff from temp group by id_diff having count(*) >= 3)

 

262. Trips and Users

hard 난이도 문제입니다. mysql을 사용했습니다.

Q. 2013년 10월 1일부터 13일까지의 취소율을 구하여라. 반올림하여 소수점 2자리까지 나타낼 것.

취소율은 ban되지 않은 사용자의 cancel 횟수를 ban되지 않은 사용자의 전체 request 수로 나눈 것이다.

풀이

• from 절의 서브쿼리
  • 날짜를 기준으로 필터링한 Trips 테이블과 ban되지 않은 users 테이블을 driver_id 기준으로 조인합니다.
  • 위의 조인된 테이블과 ban되지 않은 users 테이블을 client_id 기준으로 조인합니다.
• group by : request 날짜를 기준으로 group by
• select 절 : request 날짜, 취소율
  • 취소율 : status가 completed 면 0, 취소면 1로 표시한 뒤, 이를 합치고 전체 count 수로 나눕니다.

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

 

1393. Capital Gain/Loss

medium 난이도 문제입니다. 쿼리 실행 결과가 예시랑 똑같은데 자꾸 오류가 나길래, 왜 그런가 하고 봤더니 따옴표 문제였어요. where 조건절에 작은 따옴표 ' 대신 큰따옴표 "를 사용하면 컬럼명으로 인식하더라구요.

Q. buying과 selling이 전부 끝난 뒤의 전체 gain/loss를 구하여라

풀이

각 stock의 전체 거래 내역을 합산해야 하는 문제입니다. with문을 사용하려고 mysql 대신 ms sql을 사용했습니다.

• with 문 : operation에 따라서 view를 나눕니다.
  
  • stock의 operation day에 따라서 row_number를 계산합니다
• with문에서 생성한 view 2가지를 stock 이름과 row_number 기준으로 join하여 사용합니다.
• stock 기준으로 price 합계를 계산합니다. selling에서 buying price를 뺍니다.

with buy as (
    select stock_name, price, row_number() over(
        partition by stock_name order by operation_day) as rnb
    from stocks
    where operation = 'Buy'
), sell as (
    select stock_name, price, row_number() over(
        partition by stock_name order by operation_day) as rns
    from stocks
    where operation = 'Sell'
)

select b.stock_name, sum(s.price - b.price) as capital_gain_loss
from buy b inner join sell s
on b.stock_name = s.stock_name
and b.rnb = s.rns
group by b.stock_name

 

1613. Find the Missing IDs

medium 난이도 문제입니다. 개인적으로 with recursive문을 사용하는 게 어렵던데, 이 문제는 recursive를 사용해야 하더라구요. 그래서 개인적으로 어렵게 느껴졌습니다.

Q. missing ID를 찾아라.

missing ID는 테이블에 존재하지는 않지만, 1과 최고 id 사이에 존재하는 값이다.

풀이

• with recursive문 : 1부터 max(id) 사이의 모든 id를 구하는 데에 사용합니다. union all을 사용하여 이전 view와 +1씩 증가하는 id를 합칩니다. 이때, where 절을 사용하여 id가 max를 넘어가지 않도록 조정합니다.
• with recursive문으로 생성한 view에서 기존 테이블에 포함된 id를 제외합니다.

with recursive seq(n) as (
    select 1
    union all
    select n+1 
    from seq where n+1 <= (select max(customer_id) from customers)
)

select n as ids
from seq
where n not in (select customer_id from customers)