无上清颜

分类

题解(49)

/ 注册

全部文章（共49篇）

题解 | #平均活跃天数和月活人数#

#该题的重点在于计算不同用户月活天数的总和，会有天数重复，要用distinct函数划分时间。另外，注意是2021年数据 select date_format(start_time,"%Y%m") as month, round(count(distinct uid,date_forma...

Mysql

2021-10-27

9 1529

属实有点绕

with t as ( select *,ROW_NUMBER()over(partition by job order by score desc) as t_rank,count(1)over(partition by job) as num from grade...

Mysql

2021-10-15

2 360

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

with t as ( select user_id,date,lead(date,1)over(partition by user_id) as dtime from login # where (user_id,date) in (select user_id,min(d...

Mysql

2021-10-13

1 371

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

with t as ( select user_id,date from login where (user_id,date) in (select user_id,min(date) from login group by user_id) ) select t1.dat...

Mysql

2021-10-13

0 258

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

with t as ( select user_id,date from login where (user_id,date) in (select user_id,min(date) from login group by user_id) ) select t1.dat...

Mysql

2021-10-13

0 365

逻辑易理解

#1.建立临时表，计算date+1 with t as ( select *,lead(date,1)over(partition by user_id order by date) as dtime from login ) #2.首先筛选出用户第一天登录的数据，再利用sum和d...

Mysql

2021-10-13

1 333

逻辑相对清晰

# 创建临时表，筛出不符合条件的对象 with t as ( select * from email where send_id not in(select id from user where is_blacklist=1 ) and receive_id n...

Mysql

2021-10-13

1 361

窗口函数一步到位？

select distinct emp_no,growth from (select emp_no, LAST_VALUE(salary)over(partition by emp_no order by from_date range between unbounded preceding and...

Mysql

2021-09-27

0 389

题解 | #查找在职员工自入职以来的薪水涨幅情况#

select distinct emp_no,growth from (select emp_no, LAST_VALUE(salary)over(partition by emp_no order by from_date range between unbounded preceding and...

Mysql

2021-09-27

0 306