[TIL]_2025.04.08 본캠프 51일차 (1) : 코드카타 SQL

문제 95번. [Queries Quality and Percentage]
(https://leetcode.com/problems/queries-quality-and-percentage/description/)

with ratio as(
    select *, (rating / position) as ratio,
    case when rating < 3 then 1 
    else 0 
    end as poor_query
    from queries
)
select 
    query_name, 
    round(sum(ratio) / count(*), 2) as quality, 
    round(sum(poor_query) / count(*) * 100, 2) as poor_query_percentage
from ratio
group by query_name

문제 96번. [Monthly Transactions I]
(https://leetcode.com/problems/monthly-transactions-i/description/)

with monthly as (
select 
    *,
    date_format(trans_date, '%Y-%m') as month
from transactions
)
select 
    m.month, 
    m.country,
    count(m.id) as trans_count,
    count(t.id) as approved_count,
    sum(m.amount) as trans_total_amount,
    coalesce(sum(t.amount), 0) as approved_total_amount
from monthly m
left join (
    select *
    from transactions 
    where state = 'approved') t
on m.id = t.id
group by m.month, m.country

🚨SUM() 안에 조건을 집어넣어도 작동한다!

select
    date_format(trans_date, '%Y-%m') as month,
    country,
    count(id) as trans_count,
    sum(state = 'approved') as approved_count,
    sum(amount) as trans_total_amount,
    sum((state = 'approved') * amount) as approved_total_amount
from transactions
group by 1, 2

SUM()의 경우, 숫자를 집계하는 함수이기 때문에 True=1, False=0인 논리 연산을 괄호안에 넣어 사용할 수 있다.
마찬가지로 아래의 집계함수도 동일한 방식으로 작동하여 응용할 수 있다.

• AVG()

AVG(state = 'approved') 
-- 승인 비율 (ex. 0.7 = 70%)

아래의 집계함수는 CASE WHEN과 함께 사용하여 응용할 수 있다.

MAX(CASE WHEN state = 'approved' THEN amount ELSE NULL END)
COUNT(CASE WHEN state = 'approved' THEN 1 END)

집계함수 내부에 조건을 넣는 것은 유용하므로 꼭 기억하도록 하자!

문제 97번. [Immediate Food Delivery II]
(https://leetcode.com/problems/immediate-food-delivery-ii/)

select round(sum(order_date = customer_pref_delivery_date) / count(*) * 100, 2) as immediate_percentage
from (
    select 
        *, 
        row_number() over(partition by customer_id order by order_date) as order_no
    from delivery) as a
where order_no = 1

🚨WHERE 에 IN을 사용한 풀이 방법

select 
    round(avg(order_date = customer_pref_delivery_date)*100, 2) as immediate_percentage
from delivery
where (customer_id, order_date) in (
  select customer_id, min(order_date) 
  from Delivery
  group by customer_id
);

WHERE절에 고객의 첫번째 주문을 필터링 하기 위해서 IN을 사용한다. IN에도 서브쿼리를 사용할 수 있다.
서브쿼리와 일치하는 customer_id, order_date 컬럼만 가져온 뒤, 해당 조건에서 평균을 계산하는 AVG()를 사용하면 첫주문 중 order_date와 customer_pref_delivery_date가 일치하는 조건을 찾아서 계산할 수 있다.

문제 98번. [Game Play Analysis IV]
(https://leetcode.com/problems/game-play-analysis-iv/description/)

select 
	round(count(distinct player_id)/(select count(distinct player_id) from activity), 2) as fraction
from 
	activity
where (player_id, subdate(event_date, interval 1 day)) 
	in (select player_id, min(event_date) as first_login 
    	from activity group by 1)

이 문제도 위의 문제와 마찬가지로 WHERE절의 IN에 서브쿼리를 사용했으며, SELECT절에도 서브쿼리를 사용했다.
서브쿼리를 사용한 문제에 익숙해지자.