求过去半年内连续30天以上每天都有1000元以上成交的商铺

订单表T（user_id, shop_id, product_id, price, trans_dt）。 Q1: 求过去半年内连续30天以上每天都有1000元以上成交的商铺。

//1

select shop_id, f_date,count(1) as days from ( select
*,date_sub(t.trans_dt,rn) as f_date from (select

• ,ROW_NUMBER() over (partition by shop_id order by trans_dt) as rn from ( select shop_id,substr(trans_dt,1,10) as trans_dt,sum(price) as
  price from T group by shop_id,substr(trans_dt,1,10) having
  sum(price)>1000 ) )a )b group by shop_id, f_date having count(1) >=30

//2

select shop_id from ( select shop_id,trans_dt,lead(trans_dt)
over(partition by shop_id order by price order by trans_dt ) as n_day
from ( select shop_id,substr(trans_dt,1,10) as trans_dt,sum(price) as
price from T group by shop_id,substr(trans_dt,1,10) having
sum(price)>1000 )a )b where datediff(n_day,trans_dt)=1

Q2：随机取20个抽奖

select * from Q1 order by rand() limit 10;