问题:求一个月用户连续活跃3天以上的用户
数据格式如下:
cust_no | Active date |
1 | 20211201 |
1 | 20211202 |
1 | 20211203 |
1 | 20211204 |
1 | 20211205 |
2 | 20211201 |
2 | 20211209 |
3 | 20211205 |
3 | 20211206 |
3 | 20211207 |
4 | 202112010 |
解题思路一:使用row_number() over()
with t1 as(
select
cust_no,
actve_date,
row_number()over(partition by cust_no order by active_date) as rn
from tmp
)
select
cust_no,
active_date+rn, #如果联系刚好等于同一天
count(*) as cnt
from t1
group by cust_no,
active_date+rn
having cnt>=3
解题思路二:使