Hive sql 常见面试题-找出销售额连续3天超过100的商品

1. 题目需求

从订单详情表（order_detail）中找出销售额连续3天超过100的商品

结果如下：

2. 需要用到的表

订单明细表：order_detail

3. 查询sql

select
DISTINCT(C.sku_id)
from 
(select
B.sku_id,B.r2,
count(1) as nums
from 
(select
A.sku_id,
date_add(A.d1 , - row_number()over(partition by A.sku_id order by A.d1)) as r2
from 
(select
sku_id,
date_format(create_date,'yyyy-MM-dd') as d1,
sum(price * sku_num) as sums
from order_detail
group by sku_id,date_format(create_date,'yyyy-MM-dd') 
HAVING sums >= 100)A)B
group by B.sku_id ,B.r2
HAVING nums >= 3)C