牛客987852806号

分类

题解(45)

/ 注册

全部文章（共166篇）

题解 | #计算每天的新用户占比#

#新简化写法 select t.login_date as dt, count(distinct uid) as total_user_num, concat( ifnull( round( ( count(distinct case when t.m=1 then uid end)/count...

2024-02-28

0 282

题解 | #计算每个人的平均使用周期#

#吐槽VIP真烂到家了，你要这么跟我说题意，我肯定会哪怕你按照每周平均活跃天数(7*用户登录活跃次数/(当前天日期-第一次用户登录日期))我也会啊，什么玩意啊。 #第一次登录到当前天的时间间隔：当前天日期-第一次用户登录日期 # 平均活跃天数(用户登录次数/(当前天日期-第一次用户登录日期)) # ...

2024-02-28

1 361

题解 | #对比一周的播放完成率变化#

with tiaojian as ( select weekday(start_time)+1 as weekdate, (sum(case when timestampdiff(second,start_time,end_time)>=time_len then 1 else timesta...

2024-02-28

0 282

题解 | #每个人的累计搜索点击数排名#

#要我说出题的人就有毛病，你直接说输出小于等于3的数值就完了，还在排名+1 我真是服了。 #我都不知道那边全了，是搜素的人多还是点击的人多，毕竟需要有可能有一边是空值，服了。 with tiaojian as ( select uid, count(query_kw) as search_num...

2024-02-27

0 269

题解 | #最近7天每天的人均停留时长和次均有效时长#

#题不难，让牛客表述的很难了就2点，1.求近7天的人均时长=总时长/总人数 2.次均有效时长=符合开始和结束时间大于等于3的总时长/ #符合开始和结束时间大于等于3的总时长总人数 select dt, round( sum(diff)/count(distinct uid),1) as avg_...

Mysql

2024-02-27

0 340

题解 | #点击率排名与转化率排名差异#

with tiaojian as ( select uid, sum(is_click)/count(id) as ctr, sum(is_convert)/sum(is_click) as cvr, dense_rank()over(order by sum(is_convert)/sum(is...

2024-02-27

0 278

题解 | #各用户活跃分层人数统计#

with tiaojian as ( select t.id, t.uid, t.login_date, t.zj, t.zz, t.now_day, t.pt from( select id,uid, login_date, max(login_date)over(partition by u...

2024-02-27

0 306

题解 | #广告点击率排名#

with tiaojian as ( select t.uid, t.m from( select uid, count(rid) as cnt, rank()over(order by sum(is_click)/count(rid) desc,uid desc) as m from use...

2024-02-27

1 249

题解 | #统计最大连续登录天数区间#新手简单思路，容易理解

with tiaojian as ( select uid, login_date, row_number()over(partition by uid order by login_date asc) as m from user_login_tb group by uid,login_dat...

2024-02-26

0 312

题解 | #找出待召回的流失用户#小白简化写法

select uid, count(distinct login_date) as days, count(id) as times from user_login_tb group by uid having datediff((select max(login_date) from user_...

2024-02-26

0 270