16、从订单信息表（order_info）和用户信息表（user_info）中，分别统计每天男性和女性用户的订单总金额，如果当天男性或者女性没有购物，则统计结果为0

期望结果如下：

需要用到的表：

订单信息表：order_info

用户信息表：user_info

代码

with t as (
select  
distinct a.create_date  
,sum(if(b.gender='男',a.total_amount ,0) ) over(partition by a.create_date,b.gender rows BETWEEN unbounded preceding and unbounded following) as total_amount_male 
,sum(if(b.gender='女',a.total_amount ,0) ) over(partition by a.create_date,b.gender rows BETWEEN unbounded preceding and unbounded following) as total_amount_female 
from order_info a 
left join user_info b on a.user_id=b.user_id
)
select 
create_date
,sum(total_amount_male) as total_amount_male
,sum(total_amount_female) as total_amount_female
from t 
group by create_date

17、查询截止每天的最近3天内的订单金额总和以及订单金额日平均值，保留两位小数，四舍五入。

期望结果如下：

需要用到的表：

订单信息表：order_info

代码

select create_date
,round(sum(total_amount) over (order by datediff(create_date,'2010-01-01') range BETWEEN 2 preceding and current row ) ,2) as total_3d
,round(avg(total_amount) over (order by datediff(create_date,'2010-01-01') range BETWEEN 2 preceding and current row ),2) as avg_3d
from (
select 
create_date
,sum(total_amount) as total_amount
from order_info
group by create_date
)a

18、从订单明细表(order_detail)中查询出所有购买过商品1和商品2，但是没有购买过商品3的用户

期望结果如下：

需要用到的表：

订单信息表：order_info

订单明细表：order_detail

代码

-- 有没有买商品1和2
with t as (
select 
distinct a.user_id
,b.sku_id
,if(b.sku_id in('1','2'),1,0) as is_sale12
,if(b.sku_id ='3' ,1,0) as is_sale3
from order_info a 
left join order_detail b on a.order_id = b.order_id
)
-- 筛选数据
select
user_id
from 
(select user_id,sum(is_sale12) as is_sale12,sum(is_sale3) as is_sale3 from t group by user_id
)awhere is_sale12>0 and is_sale3=0

19、从订单明细表（order_detail）中统计每天商品1和商品2销量（件数）的差值（商品1销量-商品2销量）

期望结果如下：

需要用到的表：

订单明细表：order_detail

代码

with t as (
-- 日期 商品 销量
select 
create_date
,sku_id
,sum(if(sku_id='1',sku_num,0)) as sku_num1
,sum(if(sku_id='2',sku_num,0)) as sku_num2
from order_detail
where sku_id in('1','2')
group by 
create_date
,sku_id
)-- 日期 diff
select
create_date
,cast(sku_num1 as int) - cast(sku_num2 as int) as diff
from (
select create_date, sum(sku_num1) as sku_num1,sum(sku_num2) as sku_num2 from t
group by create_date
)a

20、从订单信息表（order_info）中查询出每个用户的最近三个下单日期的所有订单

期望结果如下：

需要用到的表：

订单信息表：order_info

代码

with t1 as (
-- 用户 订单号 日期 排序 
select 
user_id
,order_id
,create_date
,dense_rank() over (partition by user_id order by create_date desc ) as rk 
from order_info
)
select  
user_id
,order_id
,create_datefrom t1 where rk <=3 
order by user_id,order_id