Leetcode：(SQL)(SQL 50)(Sub Query) Restaurant Growth

題目連結：https://leetcode.com/problems/restaurant-growth/description/?envType=study-plan-v2&envId=top-sql-50

題意解析

• 每一天會有一到多筆購買資訊，包含購買的人，購買的數量
• 算出七天（今天＋前六天）的銷售總量和平均銷售量

解題思維

• 用 window取最近７天的總量，再除以７就可以得到平均量

實作程式碼

# 計算每一天對應過去七天的總數
WITH get_sum AS (
    SELECT DISTINCT visited_on, 
        SUM(amount) OVER(ORDER BY visited_on RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) AS amount
    FROM Customer
),
# 得到起算日（第一個包含七天的日期）
get_first_date_for_calculate AS (
    SELECT DATE_ADD(MIN(visited_on), INTERVAL 6 DAY) AS first_date
    FROM Customer
)
SELECT visited_on,
        amount,
        ROUND(amount/7, 2) As average_amount
FROM get_sum AS GS
JOIN get_first_date_for_calculate AS GF
ON GS.visited_on >= GF.first_date

解題思維

• 參考他人的作法：https://leetcode.com/problems/restaurant-growth/solutions/3673106/best-optimum-solution-with-explanation-b-uovm
• 充分利用 SQL執行時先 Where 才 Select，先用 Where 過濾日期後，Select 中就只有需要計算的日期。

實作程式碼

# Write your MySQL query statement below
# 得到起算日（第一個包含七天的日期）
WITH get_first_date_for_calculate AS (
    SELECT DATE_ADD(MIN(visited_on), INTERVAL 6 DAY) AS first_date
    FROM Customer
)
SELECT
    C.visited_on,
    # 計算每一天對應過去七天的總數
    # SQL 的執行順序是 From -> Where -> Group By -> SELECT
    # 已經在 where 過濾，只剩需要計算的日期，因此 C.visited_on 只會包含需要計算的日期
    (
        SELECT SUM(amount)
        FROM Customer
        WHERE visited_on BETWEEN DATE_SUB(C.visited_on, INTERVAL 6 DAY) AND C.visited_on
    ) AS amount,
    (
        SELECT ROUND(SUM(amount)/7, 2)
        FROM Customer
        WHERE visited_on BETWEEN DATE_SUB(C.visited_on, INTERVAL 6 DAY) AND C.visited_on
    ) AS average_amount
FROM Customer AS C
INNER JOIN get_first_date_for_calculate AS G
ON C.visited_on >= G.first_date
GROUP BY C.visited_on