2023-05-22 12:24 大数据开发工程师
关注
SQL大厂面试题分享：
#SQL面试#-- 创建用户登录数据表
CREATE TABLE user_login(
user_id INT,
login_date DATE
);
-- 插入模拟数据
INSERT INTO user_login VALUES
(1, '2022-01-01'),
(1, '2022-01-02'),
(1, '2022-01-03'),
(1, '2022-01-05'),
(1, '2022-01-06'),
(1, '2022-01-09'),
(1, '2023-01-01'),
(2, '2022-01-01'),
(2, '2022-01-03'),
(2, '2022-01-04'),
(2, '2022-01-06'),
(2, '2022-01-07'),
(2, '2022-01-08'),
(3, '2022-01-01'),
(3, '2022-01-02'),
(3, '2022-01-04'),
(3, '2022-01-05'),
(3, '2022-01-07'),
(3, '2022-01-08');
--找出所有连续未登录5天及以上的用户并提取出这些用户最近一次登录的日期
CREATE TABLE user_login(
user_id INT,
login_date DATE
);
-- 插入模拟数据
INSERT INTO user_login VALUES
(1, '2022-01-01'),
(1, '2022-01-02'),
(1, '2022-01-03'),
(1, '2022-01-05'),
(1, '2022-01-06'),
(1, '2022-01-09'),
(1, '2023-01-01'),
(2, '2022-01-01'),
(2, '2022-01-03'),
(2, '2022-01-04'),
(2, '2022-01-06'),
(2, '2022-01-07'),
(2, '2022-01-08'),
(3, '2022-01-01'),
(3, '2022-01-02'),
(3, '2022-01-04'),
(3, '2022-01-05'),
(3, '2022-01-07'),
(3, '2022-01-08');
--找出所有连续未登录5天及以上的用户并提取出这些用户最近一次登录的日期
全部评论
不讲武德的大菠萝很爱看剧
数据分析师
各位大佬，看看我的咋样，用hive写的
select t1.user_id ,t2.recent_login_date from (
select
user_id ,
login_date ,
datediff(login_date ,lag(login_date) over(partition by user_id order by login_date ) ) as dt
from user_login
) t1
left join
(select max(login_date) recent_login_date,user_id from user_login group by user_id )t2 on t2.user_id = t1.user_id
where t1.dt >=5
送花
回复
分享
牛客529442748号
WITH user_login_intervals AS (
SELECT
user_id,
login_date,
LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_login_date,
DATEDIFF(login_date, LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date)) AS days_since_last_login
FROM user_login
)
SELECT
user_id,
MAX(login_date) AS last_login_date
FROM user_login_intervals
WHERE days_since_last_login >= 5 OR prev_login_date IS NULL
GROUP BY user_id;
送花
回复
分享
秋招专场
校招火热招聘中
官网直投
泪灼
门头沟学院 计算机类
rank()等差数列
送花
回复
分享
胡哇哇
运维工程师
-- 思路：mysql8.0以下版本不开窗，用户，访问时间排序，利用自增Id,差序关联
-- 用户访问时间-下次访问时间，差值=1为连续访问，差值>1为跳空，差值>5为连续5天未登录
set @id_row:=0;
set @id_next_row:=0;
select * from
(select t1.user_id,t1.next_login,t2.login_date,
datediff(t1.next_login,t2.login_date) as diff,
t1.id_next_row,t2.id_row
from
(select
user_id,
login_date as next_login,
(@id_next_row:=@id_next_row+1) as id_next_row
from user_login
order by user_id,login_date) t1
left join
(select
user_id,login_date,
(@id_row:=@id_row+1) as id_row
from user_login
order by user_id,login_date) t2
on t1.id_next_row = t2.id_row+1 and t1.user_id=t2.user_id
) t
where diff >5
送花
回复
分享
相关推荐
投递淘天集团等公司10个岗位 > 点赞 评论 收藏
转发
点赞 评论 收藏
转发
点赞 评论 收藏
转发
05-09 18:47
吉林财经大学 工商管理类 点赞 评论 收藏
转发