题目来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/restaurant-growth
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
本人思路:
– 1、先找到customer表中去重的访问日期作为临时表temp1 ,内连接customer表,条件是temp1表的日期-customer表日期相差0-6天
– 2、where筛出日期在7天以后的日期
– 3、再按由2筛的日期分组,即可用聚合函数求出与该日期相差0-6天的营业额总数,以及7天内的平均营业额
select temp1.visited_on,
sum(c.amount)as amount,
round(sum(c.amount)/7,2) as average_amount
from (select distinct visited_on from customer)as temp1 inner join customer as c
on datediff(temp1.visited_on, c.visited_on) between 0 and 6
where temp1.visited_on >=(select min(visited_on) from customer)+6
group by temp1.visited_on
order by temp1.visited_on