문제
입력 코드
1.
SELECT visited_on,
SUM(SUM(amount)) OVER (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS amount,
ROUND(AVG(SUM(amount)) OVER (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW),2) AS average_amount
FROM Customer
GROUP BY visited_on
ORDER BY visited_on
LIMIT 999999 OFFSET 6;
2.
SELECT visited_on, amount, ROUND(average_amount,2) AS average_amount
FROM
(SELECT
visited_on,
SUM(amount) OVER (ORDER BY visited_on ROWS 6 PRECEDING) AS amount,
AVG(amount) OVER (ORDER BY visited_on ROWS 6 PRECEDING) AS average_amount
FROM (SELECT visited_on, SUM(amount) amount FROM Customer GROUP BY visited_on) AS a
) AS b
WHERE DATEDIFF(visited_on, (SELECT MIN(visited_on) FROM Customer)) >= 6;
코드 설명
#SELECT #SUM #OVER #WINDOW #PRECEDING #ROUND #AVG #GROUP BY #ORDER BY #LIMIT #OFFSET #DATEDIFF #MIN
문제 출처
https://leetcode.com/problems/restaurant-growth/?envType=study-plan-v2&envId=top-sql-50
'SQL' 카테고리의 다른 글
#116. [LeetCode / MySQL] 585. Investments in 2016 (0) | 2024.06.19 |
---|---|
#115. [LeetCode / MySQL] 602. Friend Requests II: Who Has the Most Friends (0) | 2024.06.19 |
#113. [LeetCode / MySQL] 1341. Movie Rating (0) | 2024.06.18 |
#112. [LeetCode / MySQL] 626. Exchange Seats (0) | 2024.06.17 |
#111. [LeetCode / MySQL] 1978. Employees Whose Manager Left the Company (0) | 2024.06.17 |