漏斗分析之SQL示例

分析需求条件如下

注册 -> 新手引导 -> 充值 -> 退出
register->guidance->recharge->logout

时间跨度2021-03-30~2021-03-31 窗口期1天

SQL示例如下

with t1 as (
    select _accountid,cast(_time as timestamp) as _time,date from register where date>='2021-03-30' and date <= '2021-03-31' 
),
t2 as (
    select _accountid,cast(_time as timestamp) as  _time,date from guidance where date>='2021-03-30' and date <= '2021-03-31'  
),
t3 as (
    select _accountid,cast(_time as timestamp) as  _time,date from recharge where date>='2021-03-30' and date <= '2021-03-31'  
),
t4 as (
    select _accountid,cast(_time as timestamp) as  _time,date from logout where date>='2021-03-30' and date <= '2021-03-31' 
)
select t1.date,
count(distinct t1._accountid) step1,
count(distinct t2._accountid) step2,
count(distinct t3._accountid) step3,
count(distinct t4._accountid) step4
from t1 
left join t2 on t1._accountid=t2._accountid and t1._time<t2._time and date_diff('second',t1._time,t2._time)<86400
left join t3 on t2._accountid=t3._accountid and t2._time<t3._time and date_diff('second',t1._time,t3._time)<86400
left join t4 on t3._accountid=t4._accountid and t3._time<t4._time and date_diff('second',t1._time,t4._time)<86400
GROUP BY t1.date

结果如下