Date 강의 / leetcode 1321 질문

# [Setting]

USE PRACTICE;
DROP TABLE Customer;
CREATE TABLE Customer (customer_id int, name varchar(20), visited_on date, amount int);
INSERT INTO Customer (customer_id, name, visited_on, amount)
VALUES
(44, 'Ashley', '2019-01-04', 160),
(23, 'Sabo', '2019-01-04', 70),
(38, 'Moustafa', '2019-01-05', 90),
(30, 'Halley', '2019-01-06', 140),
(5, 'Elvis', '2019-01-07', 160),
(12, 'Leslie', '2019-01-08', 100),
(23, 'Sabo', '2019-01-08', 90),
(13, 'Will', '2019-01-09', 170),
(20, 'Brock', '2019-01-10', 160),
(29, 'Leo', '2019-01-10', 90),
(33, 'Isaac', '2019-01-11', 60),
(46, 'Selena', '2019-01-12', 100),
(4, 'Winston', '2019-01-13', 150),
(15, 'Marti', '2019-01-13', 160);
SELECT * FROM Customer;

# [my practice]
select b.visited_on, 
sum(amount) as amount, 
round(sum(a.amount) /7, 2) as average_amount
from customer a
inner join
(select distinct date_sub(visited_on, interval 6 day) as week_before, 
visited_on 
from customer) b
on a.visited_on between b.week_before and b.visited_on
group by b.visited_on
having count(amount) >= 7
order by b.visited_on;

올려주신 방식 외에 inner join시 date 구간으로 조건 넣는 방식으로 시도해 보았습니다.

위 데이터로 삽입하는 경우, MySQL Workbench에서는 예상 결과대로 출력 되는데 leetcode에서 오류가 나서 혹시 제가 코드 상에서 간과하고 있는 부분이 있는지 궁금해서 질문 드립니다.

leetcode 내에 PostgreSQL로 유사한 방식의 해결 법은 확인했었는데 혹시나 해서 링크 올려봅니다.

https://leetcode.com/problems/restaurant-growth/solutions/5040002/well-explained-postgresql-solution-beats-99

select b.visited_on,
sum(amount) as amount,
round(sum(a.amount) /7, 2) as average_amount
from customer a
inner join (
    select distinct date_sub(visited_on, interval 6 day) as week_before, 
    visited_on 
    from customer
) b
on a.visited_on between b.week_before and b.visited_on
group by b.visited_on
having count(distinct a.visited_on) = 7
order by b.visited_on;