[SQL문제풀이] - Sub query의 활용

2025.02.24 - [프로그래밍/SQL, Hive, SAS 관련 정보] - [SQL] Table of Contents

---

https://leetcode.com/problems/restaurant-growth/solutions/3673106/best-optimum-solution-with-explanation/?envType=study-plan-v2&envId=top-sql-50

 

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;

 

select distinct visited_on,
        sum(amount) over w as amount,
        round((sum(amount) over w)/7, 2) as average_amount
from customer
WINDOW w AS ( 
        order by visited_on
        range between interval 6 day PRECEDING and current row
)
Limit 6, 999