刷个题先

分类

/ 注册

全部文章（共27篇）

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

select emp_no,sum(g1) growth from ( select emp_no,salary-lag(salary)over(partition by emp_no order by to_date) g1 from salaries ) as t1 group by e...

2023-04-12

1 344

题解 | 薪水第二多员工的emp_no及对应薪水salary

with t1 as (select emp_no,salary from salaries where salary!=( select max(salary) from salaries )) select t1.emp_no,salary...

2023-04-12

0 293

题解 | #获取每个部门中当前员工薪水最高的相关信息#

select t1.dept_no,emp_no,maxSalary from ( select dept_no,max(salary) maxSalary from dept_emp de left join salaries s on de.emp_no=s.emp_no group b...

2023-04-11

0 230

题解 | #每月及截止当月的答题情况#

With t1 as ( select distinct date_format(start_time,'%Y%m') start_month,uid, min(date_format(start_time,'%Y%m'))over(partition by uid order by date_fo...

2023-04-11

0 296

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

select t2.author_id,author_level,max(num) days_cnt from ( select author_id,count(*) num from (select distinct author_id,answer_date,dense_rank()over...

2023-04-11

1 441

题解 | #某宝店铺动销率与售罄率#

select style_id,round(sum(rt1)/(sum(inventory)-sum(rt1))*100,2) pin_rate, round(sum(GMV1)/sum(tag_price*inventory)*100,2) from( select distinct st.ite...

2023-04-06

0 353

题解 | #每天的日活数及新用户占比#

select dt,count(uid) dau, round(sum(if(dt=new_dt,1,0))/count(uid),2) uv_new_ratio from ( select uid, min(date(in_time)) new_dt from tb_user_log grou...

2023-02-14

0 264

题解 | #统计活跃间隔对用户分级结果#

select user_grade, round(count(uid)/max(user_cnt),2) ratio from( select uid,user_cnt, case when ld>=date_sub(cur_dt,interval 6 day) and fd<date_...

2023-02-14

0 254

题解 | #近一个月发布的视频中热度最高的top3视频#

select video_id, round((100*a+5*b+3*c+2*d)/(e+1)) hot_index from ( select tbu.video_id, avg(if(timestampdiff(second,start_time,end_time)>=duration,...

2023-02-13

0 216

题解 | #每类视频近一个月的转发量/率#

select tag, sum(if_retweet) retweet_cut, round(sum(if_retweet)/count(if_retweet),3) retweet_rate from tb_user_video_log tbu left join tb...

2023-01-17

0 275