背景:产品做了一个周期7天活动,用户可以每一天解锁一个任务,完成后发放奖励;
需求:用户连续登陆明细数据及连续登陆天数;
目的:用户参与活动的兴趣度;
活动海报图:
分析思路:
明细数据:
-- 根据用户ID分组按日期排序,将日期和分组序号相减得到连续登陆的开始日期,如果开始日期相同说明连续登陆
- 日期减去分区排序的序号,如果新日期相等,则连续登陆
汇总结果
附上代码
with, tmp_da_user as (
select
to_date(ds,'yyyymmdd') as ds
,uid
,count(distinct ds) as 完成天数
from tmp_t_user_takse_detail
where status_name = '完成'
and uid in ('203111313364823804','1833903495100023')
group by
ds
,uid
)
, tmp_da_user_diff as (
select
ds
,uid
,完成天数
,dateadd(ds, -ROW_NUMBER() OVER(PARTITION BY uid ORDER BY ds asc ),'dd') AS diff_rn
-- 注意这里是减法;日期减去分区排序的序号,如果新日期相等,则连续登陆
from tmp_da_user
)
select
uid
,min(ds) as start_ds -- 连续登陆的开始时间
,max(ds) as end_ds -- 连续登陆的结束时间
,count(1) as continuous_day -- 连续登陆的天数
,datediff(diff_rn, lag(diff_rn, 1) over(partition by uid order by diff_rn),'dd') interval_day-- 间隔多少天没交易
from tmp_da_user_diff
group by
uid
,diff_rn
补充问题:
如何取用户连续登陆超过3天的明细数据?