牛客574098914号

分类

题解(18)

/ 注册

全部文章（共18篇）

题解 | #对所有员工的薪水按照salary降序进行1-N的排名#

select emp_no, salary, dense_rank() over(order by salary desc) as rk from salaries where to_date = '9999-01-01' order by salary desc, emp_no asc ...

Mysql

2022-05-07

0 271

题解 | #统计各个部门的工资记录数#

select d.dept_no, d.dept_name, sum(if(s.salary is not null,1,0)) as sum from departments as d inner join dept_emp as de on d.dept_no = de.dept_n...

Mysql

2022-05-07

0 207

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

select emp_no, birth_date, first_name, last_name, gender, hire_date from (select *, dense_rank() over(order by hire_date desc) as rk from...

Mysql

2022-05-02

1 267

题解 | #每个6/7级用户活跃情况#

select product_id, if(show_cnt = 0, 0, round(click_cnt/ show_cnt,3)) as ctr, if(click_cnt = 0,0, round(cart_cnt/ click_cnt,3)) as cart_rate, ...

Mysql

2022-05-01

0 329

题解 | #每个6/7级用户活跃情况#

select start_month, count(distinct uid) as mau, sum(new) as month_...

Mysql

2022-04-30

0 292

题解 | #每个6/7级用户活跃情况#

with t1 as ( select uid, exam_id, score, max(score) over(partition by exam_id) as max_score, min(score) over(partition by exam_id) as min_scor...

Mysql

2022-04-30

0 353

题解 | #每个6/7级用户活跃情况#

select uid, count(start_time) as exam_complete_cnt from (select*, dense_rank() over(partition by uid order by date_format(start_time, '%Y-%...

Mysql

2022-04-28

0 458

题解 | #每个6/7级用户活跃情况#

select uid, days_window, round(dotime* days_window / daydiff, 2) as avg_exam_cnt from ( select uid, count(start_time) as dotim...

Mysql

2022-04-28

0 354

题解 | #每个6/7级用户活跃情况#

select e.exam_id, e.duration, release_time from ( select t.exam_id, max( case when fastt = 2 then timediff ...

Mysql

2022-04-28

0 298

题解 | #每个6/7级用户活跃情况#

with table1 as( select uid , date_format(start_time,'%Y-%m') as act_month, date_format(start_time,'%Y-%m-%d') as act_days, 'exam' as type from ex...

Mysql

2022-04-27

0 303