leetcode 1321 餐館營業額變化增長

leetcode 1321 餐館營業額變化增長

select distinct c2.visited_on,
(select sum(amount) from Customer c1
 where c1.visited_on <= c2.visited_on 
 and c1.visited_on >= date_sub(c2.visited_on, interval 6 day)
) as amount,
round((select sum(amount) from Customer c1
 where c1.visited_on <= c2.visited_on 
 and c1.visited_on >= date_sub(c2.visited_on, interval 6 day)
)/7, 2) as average_amount 
from Customer c2
group by c2.visited_on
having c2.visited_on >= date_add(
        (select distinct visited_on from Customer order by visited_on asc limit 0,1)
        , interval 6 day)

==

select c1.visited_on,
       sum(c2.amount)  amount       ,
       round(sum(c2.amount)/7,2)  average_amount from 
(select distinct visited_on
     from customer)
         c1 left join customer c2
on datediff(c1.visited_on,c2.visited_on)<7 and datediff(c1.visited_on,c2.visited_on)>=0
group by  c1.visited_on
having  count(distinct c2.visited_on) = 7