存湮

分类

题解(11)

/ 注册

全部文章（共11篇）

题解 | #连续签到领金币#

from(select uid,dt,(1+#基础积分+符合条件的额外加分 case when ar=3 and sign_in=1 then 2 when ar=7 and sign_in=1 then 6 wh...

Mysql

2022-02-24

0 221

题解 | #2021年11月每天新用户的次日留存率#

from(select uid,date(out_time)ot, case when date(in_time)!=date(out_time) then -1 else 1 end as cnt from tb_user_log)a2#标记当天活跃且不跨天的用户为...

Mysql

2022-02-16

1 175

题解 | #2021年11月每天新用户的次日留存率#

from tb_user_log where date(in_time) >= (select date_sub(max(date(in_time)), interval 6 day) from t...

2022-02-14

0 167

题解 | #2021年11月每天新用户的次日留存率#

from(select md,cc,ifnull(ss,0)sss from(select ot,sum(b)ss from(select uid,ot, case when (uid,dt) in (select da.uid,daa ...

2022-02-13

0 217

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

select video_id,round((100crt+5sl+3sc+2sr)/(DATEDIFF((select max(DATE(end_time)) from tb_user_video_log),md)+1),0)hot from(select video_id,avg(if(TIME...

2022-02-12

0 207

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

select user_id,name,s1 from(select user_id,name,s1,dense_rank()over(order by s1 desc)dr from(select distinct user_id,name,sum(t1)over(partition by use...

Mysql

2022-02-06

0 195

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

select id,name,gs from(select id,name,gs,dense_rank()over(order by a.gs desc)dr from (select distinct id,name,sum(grade_num)over(partition by user_id ...

Mysql

2022-02-06

0 167

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

select name,gs from(select name,gs,dense_rank()over(order by a.gs desc)dr from (select distinct name,sum(grade_num)over(partition by user_id order by ...

Mysql

2022-02-06

0 171

题解 | #最差是第几名（二）#

select a.grade from(select grade,sum(number)over(order by grade)s from class_grade)a join class_grade cg on a.grade=cg.grade where (select sum(number)...

Mysql

2022-02-06

0 181

题解 | #异常的邮件概率#

select e.date,round(c1.cnt1/c2.cnt2,3) from email e join (select date,count(type)as cnt1 from email where type='no_completed' and send_id in (select i...

Mysql

2022-01-19

0 212