240710(+35) | leetcode 1174, 550

Leetcode 1174

Immediate Food Delivery II

고객의 첫 번째 주문은 해당 고객이 만든 주문 중 가장 이른 주문 날짜를 가진 주문입니다. 모든 고객은 반드시 하나의 첫 번째 주문을 가지고 있습니다. 고객의 선호 배송 날짜가 주문 날짜와 동일하면 그 주문은 즉시 주문이라고 합니다. 그렇지 않으면 예약 주문이라고 합니다. 모든 고객의 첫 번째 주문 중 즉시 주문의 비율을 찾는 쿼리를 작성하세요.

TABLE Definition

Solution

with order_table as(
select *,
case
    when min(order_date) = min(customer_pref_delivery_date) then 'immediate'
    else 'scheduled'
    end `order`
from Delivery
group by customer_id
)
select 
    ROUND(SUM(CASE WHEN `order` = 'immediate' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) 
    AS immediate_percentage
from order_table

• 처음엔 with문 내부에 첫주문 날짜만 필터링 하고 싶어 고객 id별로 group by하고 having=min(order_date)의 조건을 주니 custimer_id가 3인 고객의 첫 주문이 출력되지 않았다.
• group by 절과 having 절을 함께 사용할 때, 최솟값을 찾으려면 group by와 having의 사용 방식이 잘못된 경우가 있습니다. 라는 피티의 설명
• 그럼 최솟값을 immediate를 구하는 조건에 줬더니 결과값이 제대로 나왔다.

Other Approaches

1. where문에 서브쿼리

SELECT 
    ROUND(SUM
    (CASE WHEN order_date = customer_pref_delivery_date THEN 1 ELSE 0 END) 
    * 100.0 / COUNT(DISTINCT customer_id)
    , 2) 
    AS immediate_percentage
FROM Delivery
WHERE (customer_id, order_date) IN (
    SELECT customer_id, MIN(order_date) AS first_order_date
    FROM Delivery
    GROUP BY customer_id
);

• Delivery 테이블에서 각 고객(customer_id)의 첫 번째 주문(min(order_date)을 필터링
• 각 고객의 (customer_id, order_date)가 서브쿼리에서 반환된 첫 번째 주문 날짜와
  일치하는지 확인.
• customer_id에 매칭되는 order_date가 한 쌍이 모두 일치해야 된다.

2. avg( )

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
);

sum함수 대신 avg함수를 사용하여 연산.

Leetcode 550

Game Play Analysis IV

다음 날 다시 로그인한 플레이어의 비율을 보고하는 솔루션을 작성하세요. 즉, 처음 로그인한 다음 날에 다시 로그인한 플레이어 수를 세고, 이를 전체 플레이어 수로 나눈 값을 소수점 둘째 자리까지 반올림하여 구하세요. 각 플레이어가 처음 로그인한 날짜부터 최소 이틀 연속으로 로그인한 경우를 찾아야 합니다.

TABLE Definition

Solution

WITH re_log AS (
    SELECT player_id,
    DATEDIFF(event_date, min(event_date) over(PARTITION BY player_id)) = 1 AS RELOGIN
    FROM Activity
)
SELECT ROUND(SUM(relogin) / COUNT(DISTINCT player_id), 2) AS fraction
FROM re_log

• 문제에서 재로그인한 유저를 처음 로그인하고 다음날도 로그인한 유저라 명시해 줬기에, DATEDIFF가 '1'인 쿼리를 작성.
• 테이블에 player_id가 중복되어 있고, player_id별 1개의 최초 로그인 기록을 가져오기 위해 윈도우 함수 min(event_Date) over(partition by palyer_id)
• partition by는 group by와 동일한 역할을 한다
• 윈도우 함수 형식 min()/max()/avg()/sum()/rank()... over(partition by)

SUM() COUNT() & AVG()

변환이 가능한 경우

1. 전체 행 수를 기준으로 비율을 계산할 때
   논리적인 값(1 또는 0)을 가진 컬럼의 평균을 계산하여 비율을 구할 때, SUM과 COUNT 대신 AVG를 사용할 수 있습니다.

# 특정 조건을 만족하는 행의 비율을 계산할 때
SELECT AVG(condition_column) FROM table;

2. 단일 그룹의 평균을 계산할 때
   특정 그룹 내에서 평균을 계산할 때 AVG를 사용할 수 있습니다.

# 각 그룹별로 평균을 구할 때
SELECT group_id, AVG(value_column) FROM table GROUP BY group_id;

변환이 불가능한 경우

1.고유한 항목의 비율을 계산할 때
COUNT(DISTINCT column)을 사용하는 경우, 전체 행 수가 아닌 고유 항목 수를 기준으로 비율을 계산하기 때문에 AVG로 변환할 수 없습니다.

# 고유한 플레이어 수를 기준으로 비율을 계산할 때
SELECT ROUND(SUM(condition_column) / COUNT(DISTINCT player_id), 2) AS fraction FROM table;

2. 부분 집합의 합계를 계산할 때
   특정 조건을 만족하는 행의 합계를 계산하고, 이를 전체 고유 항목 수로 나누어 비율을 계산할 때는 AVG로 변환할 수 없습니다.

# 고유한 플레이어 수를 기준으로 첫 번째 로그인 다음 날 다시 로그인한 플레이어의 비율을 계산할 때
WITH re_log AS (
    SELECT player_id,
           DATEDIFF(event_date, MIN(event_date) OVER(PARTITION BY player_id)) = 1 AS RELOGIN
    FROM Activity
)
SELECT ROUND(SUM(RELOGIN) / COUNT(DISTINCT player_id), 2) AS fraction FROM re_log;