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
夏日与晚风
武汉理工大学 数据分析师
SELECT id ,MAX(date2) last_date
FROM (SELECT id,date2,last_day,DATEDIFF(DAY,last_day,date2) gap
FROM (SELECT id,date2, MAX(date2)
OVER(PARTITION BY id order by date2 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) last_day
FROM DS) TMD )TMD1
GROUP BY id
HAVING MAX(gap)>=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;
鲸鲸说数据
Georgetown University 数据分析师
连续五天未登录 这种表达方式好一些。。。
牛客880571257号
东北财经大学 策略运营
select distinct t.a ,max(login_date) from user_login left join (select user_login.user_id a,(datediff(user_login.login_date,lag(user_login.login_date) over (partition by user_login.user_id order by user_login.login_date ) ))z from user_login) t on t.a = user_login.user_id where z>5 group by user_id;
胡哇哇
燕山大学 运维工程师
-- 思路：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
泪灼
门头沟学院 大数据开发工程师
rank()等差数列
相关推荐
不愿透露姓名的神秘牛友
07-18 17:25
不午睡会死仙人Zz：分享一个自己做的GitHub面试题整理项目0voice/Campus_recruitment_interview_questions，积德积德，许愿自己和大家都能过面
点赞 评论 收藏
分享
06-13 10:15
门头沟学院 Java 点赞 评论 收藏
分享
点赞 评论 收藏
分享