60分钟内连续购买
背景，需要统计下单老板，在60分钟内是否有多个大神提供服务，运营针对性做活动，方便老板下单；
SQL思路
step1 解析
用户肯能对一个大神重复下单，因此先聚合；
代码统计如下
with tmp_da_trade_order as (
select ds
,to_date(ds,'yyyymmdd') as dt
,from_user_id
,to_user_id
,min(create_time) as min_create_time
,max(create_time) as max_create_time
from ypp_order_detail t1
where from_user_id = '04be729a5bc241cea6f4e4d187e68d9c'
group by
ds
,from_user_id
,to_user_id
)
--
,tmp_da_user_minutes as (
select
t1.ds
,t1.from_user_id
,t1.to_user_id
,t1.min_create_time
,count(distinct case when abs(datediff(t2.min_create_time,t1.min_create_time,'mi')) <= 60 then t2.to_user_id end) as 60_minutes
from tmp_da_trade_order t1
left join tmp_da_trade_order t2
on (t1.ds = t2.ds
and t1.from_user_id = t2.from_user_id)
group by
t1.ds
,t1.from_user_id
,t1.to_user_id
,t1.min_create_time
)
--
select
t1.*
,当日总下单老板数
,下单老板数/当日总下单老板数 as 60分钟内_服务大神的下单老板数占当日比率
,'老板会在多个区间重复被统计，属于正常' as mark
from (
select
ds 日期
,type as 60分钟内_服务大神
,count(distinct t1.from_user_id) as 下单老板数
from (
select *
,case when 60_minutes = 1 then '1'
when 60_minutes >=2 and 60_minutes <= 3 then '2~3'
when 60_minutes >=4 and 60_minutes <= 5 then '4~5'
when 60_minutes >=6 then '6以上' end as type
from tmp_da_user_minutes
)t1
group by
ds
,type
)t1
left join (
select
ds
,count(distinct t1.from_user_id) as 当日总下单老板数
from (
select *
from tmp_da_user_minutes
)t1
group by