Find the shops that have sold more than 1,000 yuan per day for more than 30 consecutive days in the past six months

Order table T (user_id, shop_id, product_id, price, trans_dt).Q1: Find the shops that have sold more than 1,000 yuan per day for more than 30 consecutive days in the past six months.

//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) astrans_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 random draws

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