[코드카타 스터디]sql_7(98번, 116번 문제)

문제링크

98.https://leetcode.com/problems/game-play-analysis-iv/

116.https://leetcode.com/problems/restaurant-growth/

문제 98. Write a solution to report the fraction of players that logged in again on the day after the day they first logged in, rounded to 2 decimal places. In other words, you need to count the number of players that logged in for at least two consecutive days starting from their first login date, then divide that number by the total number of players.

1. 
SELECT
  ROUND(COUNT(DISTINCT player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) fraction
FROM
  Activity
WHERE
  (player_id, DATE_SUB(event_date, INTERVAL 1 DAY))
  IN (
    SELECT player_id, MIN(event_date) first_login 
    FROM Activity 
    GROUP BY player_id
  );
  
  
 2. 
  WITH a as(SELECT player_id, datediff(event_date, min(event_date) over(partition by player_id)) = 1 as log
          FROM activity)

SELECT round(sum(log)/count(distinct player_id), 2) fraction
FROM a;


3.
SELECT ROUND(COUNT(b.player_id)/COUNT(a.player_id),2) AS fraction
FROM (SELECT player_id, MIN(event_date) AS first_login FROM Activity GROUP BY player_id) AS a
LEFT JOIN Activity AS b
ON a.player_id = b.player_id AND a.first_login +1 = b.event_date


4.
select round(count(case when datediff(a1.event_date, a2.first) = 1 then 1 end) / count(distinct a1.player_id), 2) fraction
from Activity a1
left join 
(   select player_id, min(event_date) first
    from Activity 
    group by player_id
) a2 
on a1.player_id = a2.player_id 


5.
select round(avg(target), 2) as fraction
from(   select round(avg(count(case when datediff(next_login_date, event_date) = 1 then 1 end), 2) as fraction
        from(   select *,  lead(event_date) over(partition by player_id order by event_date) as next_login_date, 
                            rank() over(partition by player_id order by event_date) as ranks
                from activity
            )q1
        where ranks = 1
        group by player_id
    )q2

best

WITH a as(SELECT player_id, datediff(event_date, min(event_date) over(partition by player_id)) = 1 as log
          FROM activity)

SELECT round(sum(log)/count(distinct player_id), 2) fraction
FROM a;

• MIN(event_date) OVER(PARTITION BY player_id): 각 플레이어(player_id)의 첫 로그인 날짜를 찾기 위해 윈도우 함수 사용. 각 플레이어별 event_date의 최솟값이 계산되어 첫 로그인 날짜가 된다.
• DATEDIFF(event_date, MIN(event_date) OVER(PARTITION BY player_id)) = 1: 현재 event_date와 첫 로그인 날짜의 차이를 계산. 그 결과가 1인 경우 log는 TRUE(또는 1)가 되고, 그렇지 않은 경우는 FALSE(또는 0)가 된다.

내가 해결한 방법.

1.플레이어별 첫 로그인 날짜를 구하기.

#MIN(event_date)를 사용하여 각 player_id에 대해 가장 빠른 로그인 날짜(최초 로그인 날짜)를 찾는다.
SELECT player_id, MIN(event_date) AS first_login 
FROM Activity 
GROUP BY player_id

->각 플레이어의 첫 로그인 날짜를 찾는다. 이를 통해 첫 로그인 날짜를 기준으로 데이터를 그룹화.
2.첫 로그인 다음 날에 로그인한 기록이 있는 플레이어를 찾기.

WHERE #DATE_SUB(event_date, INTERVAL 1 DAY)는 현재 로그인 날짜에서 하루를 뺀 날짜를 계산
  (player_id, DATE_SUB(event_date, INTERVAL 1 DAY))
  IN (
    SELECT player_id, MIN(event_date) AS first_login 
    FROM Activity 
    GROUP BY player_id
  )

Activity 테이블에서 (player_id, DATE_SUB(event_date, INTERVAL 1 DAY))가 첫 로그인 날짜와 일치하는 레코드를 찾는다.
->(player_id, DATE_SUB(event_date, INTERVAL 1 DAY))가 내부 쿼리의 (player_id, first_login)과 일치하는 경우에 해당하는 플레이어를 찾는다.즉, 첫 로그인 다음 날 다시 로그인한 기록이 있는 플레이어만 선택.

3.조건을 만족하는 플레이어 수를 전체 플레이어 수로 나누어 비율을 계산.

SELECT
  ROUND(COUNT(DISTINCT player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS fraction

->COUNT(DISTINCT player_id)는 첫 로그인 다음 날에 다시 로그인한 플레이어의 고유 수.
(SELECT COUNT(DISTINCT player_id) FROM Activity)는 전체 플레이어 수를 계산.
->이 둘을 나누어 첫 로그인 다음 날 다시 로그인한 비율을 구하고, ROUND(..., 2)를 통해 소수 둘째 자리까지 반올림.

문제 116. You are the restaurant owner and you want to analyze a possible expansion (there will be at least one customer every day).
Compute the moving average of how much the customer paid in a seven days window (i.e., current day + 6 days before). average_amount should be rounded to two decimal places.
Return the result table ordered by visited_on in ascending order.

문제 해석

식당 주인으로서, 7일 이동 평균을 사용해 고객이 지불한 금액을 분석하여 확장 가능성을 평가하려 한다.

목표: 매일 고객이 지불한 금액의 7일 이동 평균을 계산한다. 이동 평균은 해당 날짜와 그 이전 6일간의 평균 금액이다.
결과: average_amount는 소수점 둘째 자리까지 반올림하고, 결과는 visited_on 날짜를 기준으로 오름차순으로 정렬하여 반환.

이동 평균 윈도우는 현재 날짜를 포함한 7일 간의 지불 금액을 기준으로 한다.

1.
SELECT visited_on, amount, ROUND(amount/7, 2) average_amount
FROM (
    SELECT DISTINCT visited_on, 
    SUM(amount) OVER(ORDER BY visited_on RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) amount, 
    MIN(visited_on) OVER() 1st_date 
    FROM Customer
) a
WHERE visited_on>= 1st_date + 6;


SELECT visited_on, amount, ROUND(amount/7, 2) average_amount
FROM (
    SELECT DISTINCT visited_on, 
    SUM(amount) OVER(ORDER BY visited_on RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) amount
    FROM Customer
) a
WHERE visited_on >= (select MIN(visited_on) from customer) + 6;


2.
SELECT VISITED_ON
		, SUM(AMOUNT) OVER(ORDER BY VISITED_ON RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) AS 'AMOUNT'
		, ROUND(SUM(AMOUNT) OVER(ORDER BY VISITED_ON RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) / 7, 2) AS 'AVERAGE_AMOUNT'
FROM (SELECT VISITED_ON
					, SUM(AMOUNT) AMOUNT
			FROM CUSTOMER
			GROUP BY VISITED_ON) CUS
WHERE VISITED_ON >= (SELECT MIN(VISITED_ON) FROM CUSTOMER) + 6
ORDER BY VISITED_ON ASC

3.
with a as (
select visited_on, sum(amount) ta
from customer 
group by visited_on
)

select a1.visited_on, sum(a2.ta) amount, round(avg(a2.ta), 2) average_amount
from a a1, a a2
where datediff(a1.visited_on, a2.visited_on) >= 0 and datediff(a1.visited_on, a2.visited_on) <= 6 
group by a1.visited_on
having count(a2.visited_on) = 7

4.
# Write your MySQL query statement below
with a as (
select visited_on, sum(amount) amount
from customer 
group by visited_on
)

select visited_on, amount, average_amount
from(   select visited_on, 
            sum(amount) over(order by visited_on rows between 6 preceding and current row) as amount,
            round(avg(amount) over(order by visited_on rows between 6 preceding and current row),2) as average_amount,
            ROW_NUMBER() OVER (ORDER BY visited_on) AS rownum
        from customer
    )q1
where rownum >= 7

best

SELECT visited_on, amount, ROUND(amount/7, 2) average_amount
FROM (
    SELECT DISTINCT visited_on, 
    SUM(amount) OVER(ORDER BY visited_on RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) amount, 
    MIN(visited_on) OVER() 1st_date 
    FROM Customer
) a
WHERE visited_on>= 1st_date + 6;

1.각 날짜(visited_on)에 대해 과거 7일간의 금액 합계를 계산

SELECT DISTINCT 
    visited_on, 
    SUM(amount) OVER (
        ORDER BY visited_on 
        RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
    ) AS amount, 
    MIN(visited_on) OVER() AS 1st_date 
FROM Customer

• SUM(amount) OVER (ORDER BY visited_on RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW):
  각 날짜에 대해 이 날짜와 그 이전 6일간의 금액 합계를 계산. 이렇게 해서 7일 동안의 금액 합계가 amount 열에 저장.
• MIN(visited_on) OVER():
  visited_on의 최소값(즉, 첫 방문 날짜)을 1st_date로 저장. 이 값을 통해 7일 데이터가 모두 준비된 시점부터 시작하게 한다.

2.7일 이동 평균을 계산

SELECT visited_on, amount, ROUND(amount/7, 2) AS average_amount
FROM a
WHERE visited_on >= 1st_date + 6;

• WHERE visited_on >= 1st_date + 6:
  최소 7일간의 데이터가 있는 날짜부터 결과에 포함시키기 위해 사용합니다. 첫 6일간은 7일 이동 평균을 계산할 수 없으므로 제외됩니다.
• ROUND(amount/7, 2):
  amount(7일간의 금액 합계)를 7로 나누어 7일 이동 평균을 계산하고, 소수점 둘째 자리까지 반올림합니다.

사용문법

• min() over(): 전체행에 대한 최솟값.
  over(), order by, partition by
• row와 range의 차이
  row와 range의 차이
• over(order by ... rows between .. preceding and current row):
  이동평균