数据方向必考SQL 连续登录问题
#数据人的面试交流地##数据人##SQL面试#
**均为社区同学面试遇到题目**
题目:找出连续登录5天的用户
图1为模拟数据
图2为运行结果
Hsql解法如下
select
uid,
curr,
5_day
from (
select
uid,
to_date(`login_date`) curr, --当前日期
last_value(to_date(`login_date`))
over(partition by uid order by to_date(`login_date`) asc
rows BETWEEN CURRENT ROW and 4 FOLLOWING) as 5_day, -- 当前日期后四天的日期值
DATEDIFF(
last_value(to_date(`login_date`))
over(partition by uid order by to_date(`login_date`) asc
rows BETWEEN CURRENT ROW and 4 FOLLOWING),
to_date(`login_date`)) AS DRFF_DAY -- 当前日期后四天的日期值 - --当前日期 = 最近五次登录的间隔天数,间隔4才是连续登录5天
from
(select distinct * from tmp) t1 --一个用户一天可能登录多次,只保留一次
) t1 where DRFF_DAY == 4#数据人的面试交流地#
更多实战题目及解法思路都在社区!
**均为社区同学面试遇到题目**
题目:找出连续登录5天的用户
图1为模拟数据
图2为运行结果
Hsql解法如下
select
uid,
curr,
5_day
from (
select
uid,
to_date(`login_date`) curr, --当前日期
last_value(to_date(`login_date`))
over(partition by uid order by to_date(`login_date`) asc
rows BETWEEN CURRENT ROW and 4 FOLLOWING) as 5_day, -- 当前日期后四天的日期值
DATEDIFF(
last_value(to_date(`login_date`))
over(partition by uid order by to_date(`login_date`) asc
rows BETWEEN CURRENT ROW and 4 FOLLOWING),
to_date(`login_date`)) AS DRFF_DAY -- 当前日期后四天的日期值 - --当前日期 = 最近五次登录的间隔天数,间隔4才是连续登录5天
from
(select distinct * from tmp) t1 --一个用户一天可能登录多次,只保留一次
) t1 where DRFF_DAY == 4#数据人的面试交流地#
更多实战题目及解法思路都在社区!
全部评论
相关推荐
点赞 评论 收藏
转发
点赞 评论 收藏
转发