day17 SQL基础-子查询

1321. 餐馆营业额变化增长

你是餐馆的老板，现在你想分析一下可能的营业额变化增长（每天至少有一位顾客）。

写一条 SQL 查询计算以 7 天（某日期 + 该日期前的 6 天）为一个时间段的顾客消费平均值。average_amount要 保留两位小数

查询结果按 visited_on 排序。

查询结果格式的例子如下。

select
		visited_on,
		sum_amount as amount,
		round(average_amount,2) as average_amount
from
		(select 
				 visited_on,
				 sum(amount) over(order by visited_on rows 6 preceding) as sum_amount,
				 avg(amount) over(order by visited_on rows 6 preceding) as average_amount,
				 row_number() over(order by visited_on) as ranking
		from
				(select visited_on,sum(amount) as amount 
					from Customer
					group by visited_on) as t) as tt

where 
		tt.ranking>=7
order by
		1

1045. 买下所有产品的客户

写一条 SQL 查询语句，从 Customer 表中查询购买了 Product 表中所有产品的客户的 id。

示例：

select
		customer_id
from
		Customer as c 
group by
		customer_id
having
		count(distinct c.product_key)=(select count(*) from Product)

1549. 每件商品的最新订单

写一个SQL 语句, 找到每件商品的最新订单(可能有多个).

返回的结果以 product_name 升序排列, 如果有排序相同, 再以 product_id 升序排列. 如果还有排序相同, 再以 order_id 升序排列.

查询结果格式如下例所示。

select
	p.product_name,
	o.product_id,
	o.order_id,
	o.order_date
from
	Orders as o  join Products as p on p.product_id = o.product_id and
    (o.product_id,o.order_date) in(
							select
									product_id,
									max(order_date) as order_date
							from
									Orders
							group by
									product_id)
order by
		1,2,3