Juicelabxx

分类

/ 注册

全部文章（共38篇）

题解 | #牛客每个人最近的登录日期(四)#

写完这段突然感觉自己很牛，，蜜汁自信了，结果下一题又不会做。。 with new_user as ( select user_id, date, row_number() over (partition by user_id order by date) as rn fr...

2024-09-12

1 90

题解 | #牛客每个人最近的登录日期(三)#

select round(sum(1_day_reten)/sum(total_users),3) as p from (select count(distinct a.user_id) as total_users, count(distinct case ...

2024-09-11

1 84

题解 | #牛客每个人最近的登录日期(二)#

写的时候几个子查询嵌套给我整昏了，但是逻辑还是通顺的，就是括号太多了... select temp3.name, client.name, date from (select user.name, client_id, date from (select user_i...

2024-09-11

1 101

题解 | #查找入职员工时间排名倒数第三的员工所有信息#

看到很多人都是用的先去重得到倒三的hire_date再select，感觉dense rank逻辑更简单点 with ranked as (select *, dense_rank () over (order by hire_date DESC) as dr from employees) sele...

2024-09-06

2 81

没办法只能多层嵌套 | #每个月Top3的周杰伦歌曲#

with ranked as ( select month, row_number() over (partition by month order by play_pv DESC, song_id ASC) as ranking, song_name, play_...

2024-08-26

1 111

题解 | #统计各岗位员工平均工作时长#

select s.post as post, avg(timestampdiff(second, first_clockin, last_clockin)/ (60*60)) as work_hours from attendent_tb a left join staff_tb s on s.st...

2024-08-26

2 129

不用mins对得起这个题吗｜#找出每个学校GPA最低的同学#

with mingpa as ( select device_id, university, gpa, min(gpa) over (partition by university) as mins from user_profile ) select device_id...

2024-08-26

1 98

casewhen+right搞定｜#统计每种性别的人数#

select case when right(profile, 6) = 'female' then 'female' else 'male' end as gender, count(*) from user_submit group...

2024-08-26

1 74