1321. Restaurant Growth (leetcode)

여기까진 했어요..

WITH t AS (
    SELECT visited_on
        , SUM(amount) amount
    FROM Customer
    GROUP BY visited_on
)

SELECT visited_on
     , SUM(amount) OVER (ORDER BY visited_on ROWS 6 PRECEDING) AS amount
     , ROUND(AVG(amount) OVER (ORDER BY visited_on ROWS 6 PRECEDING), 2) AS average_amount
FROM t

7번째 행부터 적용되게 어떻게 하는건데..
gpt도 못알아듣잖아..

그래서 결국 solution를 들쳐봅니다

답 쿼리는

SELECT
    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,
    ROUND(
        (
            SELECT SUM(amount) / 7
            FROM customer
            WHERE visited_on BETWEEN DATE_SUB(c.visited_on, INTERVAL 6 DAY) AND c.visited_on
        ),
        2
    ) AS average_amount
FROM customer c
WHERE visited_on >= (
        SELECT DATE_ADD(MIN(visited_on), INTERVAL 6 DAY)
        FROM customer
    )
GROUP BY visited_on;

왜 아래꺼는 안돼요..? 어이가 없네요
정답 쿼리에서 WHERE절만 베껴서 추가했어요 ㅠㅠ

WITH t AS (
    SELECT visited_on
        , SUM(amount) amount
    FROM Customer
    GROUP BY visited_on
)

SELECT visited_on
     , SUM(amount) OVER (ORDER BY visited_on ROWS 6 PRECEDING) AS amount
     , ROUND(AVG(amount) OVER (ORDER BY visited_on ROWS 6 PRECEDING), 2) AS average_amount
FROM t
WHERE visited_on >= (
        SELECT DATE_ADD(MIN(visited_on), INTERVAL 6 DAY)
        FROM customer
    )