皮蛋瘦瘦粥

分类

/ 注册

全部文章（共27篇）

题解 2021年10月退货率不大于0.5的商品各项指标#

select product_id,round((click/ct),3) as ctr,round((cart/click),3) as cart_rate,round((pay/cart),3) as payment_rate, round((refund/pay),3) as refund_r...

2023-06-16

0 187

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

select user_grade,round(count(uid)/(select count(distinct uid) from tb_user_log),2) ratio from (select uid, (case when datediff((select max(date(in_...

2023-05-25

0 225

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

select a.dt, round(count(b.dt)/count(a.dt),2) as uv_left_rate from (select uid,min(date(in_time)) as dt from tb_user_log group by uid) a left join (...

2023-05-20

0 204

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

select artical_id ,max(viwer) as max_uv from (select artical_id,dt, sum(diff)over(partition by artical_id order by dt,diff DESC) as viwer from (select...

2023-05-20

0 173

题解 | #2021年11月每天的人均浏览文章时长#

select date_format(in_time,'%Y-%m-%d') as dt , round(sum(timestampdiff(second,in_time,out_time))/count(distinct uid),1) as avg_viiew_len_sec from tb_u...

2023-05-18

0 155

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

select video_id, round(((finished_rate*100+5*like_cnt+3*comment_cnt+2*retweet_cnt)/(no_play_record_day_cnt+1)),0) as hot_index from (select video_id, ...

2023-05-18

0 216

题解 | #国庆期间每类视频点赞量和转发量#

select * from (select tag, left(start_time,10) as dt, sum(sum(if_like))over(partition by tag order by left(start_time,10) rows 6 preceding) as sum_lik...

2023-05-18

0 207

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

select author, month,fans_growth_rate, sum(follow)over(partition by author order by month) as total_fans from (select author, left(start_time,7) as mo...

2023-05-18

0 193

题解 | #各个视频的平均完播率#

select t1.video_id,round(sum((case when (end_time-start_time)>=duration then 1 else 0 end))/count(*),3) as avg_comp_play_rate from tb_user_video_l...

2023-05-17

0 172

题解 | #各个视频的平均完播率#

select a.video_id,round(n/cnt,3) as avg_comp_play_rate from (select t1.video_id ,sum((case when (end_time-start_time)>=duration then 1 else 0 end))...

2023-05-17

0 177