牛客666375956号

分类

题解(56)

/ 注册

全部文章（共51篇）

题解 | #获得积分最多的人(三)#

select c.user_id as id,name,grade_num from( select user_id,dense_rank()over(order by grade_num desc)as rn,grade_num from ( select user_id,sum(case whe...

Mysql

2022-03-12

0 188

题解 | #牛客的课程订单分析(五)#

select a.user_id, min(a.date) as first_buy_date,min(a.next_date) as second_buy_date,count(*) as cnt from (select * , lead(date,1) over(p...

Mysql

2022-03-12

0 151

题解 | #考试分数(三)#

select a.id,b.name,a.score from( select id,language_id,dense_rank() over(partition by language_id order by score desc)as rn,score from grade )a,langua...

Mysql

2022-03-05

0 197

题解 | #构造一个触发器audit_log#

select `date`,round(if(fm!=0,fz/fm,0),3)as p from( select `date`,count(distinct case when diff=1 and min_date=`date` then user_id else null end)as fz,...

Mysql

2022-02-26

0 171

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

select `date`,count(case when `date`=first_date then `date` else null end)as new from login b inner join ( select user_id,min(date)as first_date from ...

Mysql

2022-02-22

0 226

题解 | #牛客的课程订单分析(四)#

先将满足条件的UID选出来，然后根据uid选出最小日期，最后count出现的次数 SELECT user_id,min(date) as fisrt_buy_date, count(case when product_name in("C++","Java","Python") then user_...

Mysql

2022-02-22

0 286

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

分子分母都要distinct SELECT round(count(distinct case when diff=1 then user_id else null end)/count(distinct user_id),3) from( SELECT t1.user_id,(t1.date-t2...

Mysql

2022-02-22

0 172

题解 | #统计salary的累计和running_total#

记得要给时间加限制！！！！ SELECT emp_no,salary,sum(salary) over(order by emp_no)as running_total from salaries WHERE to_date = '9999-01-01'

Mysql

2022-02-21

0 198

题解 | #某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级#

SELECT b.author_id,c.author_level,b.days_cnt from author_tb c, ( SELECT author_id,count(*)as days_cnt from( select distinct author_id,answer_date,DENS...

Mysql

2022-02-18

0 183

题解 | #某乎问答回答过教育类问题的用户里有多少用户回答过职场类问题#

select count(a.author_id) from (select distinct author_id from answer_tb right join issue_tb using(issue_id) where issue_type="Education")a inner j...

Mysql

2022-02-18

0 197