Liumankit

分类

/ 注册

全部文章（共23篇）

题解 | 某乎问答高质量的回答中用户属于各级别的数量（傻瓜做法）

select * from (select '5-6级' as level_cut, sum(if(author_level = 5 or author_level = 6,1,0)) as num from answer_tb left join author_tb using(author_...

2025-08-08

0 25

题解 | 牛客直播开始时各直播间在线人数

select course_id, course_name, count(*) from attend_tb a left join course_tb b using(course_id) where time(in_datetime) <= "19:00:00" a...

2025-08-08

0 22

题解 | 国庆期间近7日日均取消订单量（最傻瓜解决）

select '2021-10-01', round(count(mileage) / 7,2), round((count(*)-count(mileage)) / 7,2) from tb_get_car_record a left join tb_get_car_order b using(o...

2025-08-08

0 18

题解 | 每个城市中评分最高的司机信息

select city, driver_id, perday_rank, perday_order, perday_mileage from (select city, driver_id, round(avg(grade),1) as perday_rank, round(count(*) / ...

2025-08-08

0 24

题解 | 零食类商品中复购率top3高的商品

select product_id, round(sum(repurchase) / count(*),3) as repurchase_rate from ( select product_id,uid,if(count(*)>1,1,0) as repurchase from tb_or...

2025-08-08

0 29

题解 | 某店铺的各商品毛利率及店铺整体毛利率

select "店铺汇总", concat(round((1 - sum(in_price * cnt) / sum(price * cnt)) * 100,1),'%') from tb_order_detail a left join tb_order_overall b...

2025-08-08

0 26

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

select t2.join_time as dt, round(count(t1.uid = t2.uid) / count(*),2) as uv_left_rate from (select uid, min(date(in_time)) as join_time from tb_user_l...

2025-08-07

0 21

题解 | 每篇文章同一时刻最大在看人数

# select # artical_id, # in_time, # rank() over(partition by artical_id order by in_time) # from tb_user_log # order by artical_id,in_time select art...

2025-08-07

0 17

题解 | 每个创作者每月的涨粉率及截止当前的总粉丝量

select author, date_format(start_time, '%Y-%m') as watched_month, # sum(if(if_follow=1,1,0)), # sum(if(if_follow=2,1,0)), # count(*), round((sum(if(i...

2025-08-06

0 25

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

select tag, sum(if_retweet), round(sum(if_retweet) / count(*),3) as retweet_rate from tb_user_video_log a left join tb_video_info b on a.video_id = ...

2025-08-06

0 30