jl0427

分类

/ 注册

全部文章（共50篇）

题解 | #店铺901国庆期间的7日动销率和滞销率#

select dt,round(count(distinct product_id)/onsale_cnt,3) as sale_rate, 1 - round(count(distinct product_id)/onsale_cnt,3) as unsale_rate from (SELECT ...

2023-11-24

1 320

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

select city,driver_id,round(avg_grade,1) as avg_grade, round(avg_order_num,1) as avg_order_num, round(avg_mileage,3) as avg_mileage from...

2023-11-24

0 329

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

select uid,date_format(t2.dt,'%Y%m') as `month`,sum(t2.coin) as coin from ( select *,row_number() over (PARTITION BY t1.uid,t1.dt2 order by t1.dt) as ...

2023-11-23

0 338

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

select product_id, round(sum(purse)/count(purse),3) as repurchase_rate from( select t.product_id as product_id, if(count(event_time)>1,1,0) as purs...

2023-11-23

0 312

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

select user_grade,round(count(1)/(select count(distinct uid) from tb_user_log),2) as ratio from( select uid,(case when timestampdiff(day,in_time,cur_d...

2023-11-22

1 267

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

with t1 as(select artical_id,dt,sum(uv)over(partition by artical_id order by dt,uv desc) as uv_cnt from (select uid,artical_id,in_time as dt,1 as uv f...

2023-11-22

0 208

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

SELECT video_id, ROUND((100 * comp_play_rate + 5 * like_cnt + 3 * comment_cnt + 2 * retweet_cnt) / (TIMESTAMPDIFF(DAY, recently_end_date, ...

2023-11-22

0 192

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

with t_rank as ( select *, count(score) over(partition by job) as cnt, row_number() over(partition by job order by score,id) as a, row_number() over(p...

2023-11-20

0 181

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

select t0.date,ifnull(round(count(distinct t2.user_id)/(count(t1.user_id)),3),0) as `p` from (select date from login group by date) as t0 left join (s...

2023-11-20

1 279

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

select date, sum(case when (user_id,date) in (select user_id,min(date) from login group by user_id) then 1 else 0 end) as new from login group by date...

2023-11-19

0 243