牛客40605318号

题解

/ 注册

全部文章 / 题解（共7篇）

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

select product_id, round(if(count()!=0,sum(if_click)/count(),0),3) as ctr, round(if(sum(if_click)!=0,sum(if_cart)/sum(if_click),0),3) as cart_rate, ro...

Mysql

2022-06-18

2 411

题解 | #计算商城中2021年每月的GMV#

select date_format(event_time,'%Y-%m') as month, round(sum(total_amount),0) as GMV from tb_order_overall where status = 1 or status = 0 and year(event...

Mysql

2022-06-18

0 267

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

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

Mysql

2022-06-18

0 240

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

select date(in_time) dt, round(sum(timestampdiff(second,in_time,out_time)) / count(distinct uid),1) avg_lensec from tb_user_log where date_format(in_t...

Mysql

2022-06-12

0 298

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

select c.t, sum(c.re) as retweet_cut, round(sum(c.re)/count(c.re),3) as retweet_rate from (select a.video_id, b.tag as t, a.start_time as time, a.if_r...

Mysql

2022-06-12

0 288

题解 | #平均播放进度大于60%的视频类别#

select c.t, concat(round(avg(c.p)*100,2),'%') as per from (select a.uid, a.video_id, b.tag as t, if((timestampdiff(second,a.start_time,a.end_time)/b.d...

Mysql

2022-06-12

1 307

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

select c.video_id, round(sum(c.if_finish)/count(c.if_finish),3) as avg_comp_play_rate from (select a.uid, a.video_id, a.start_time, a.end_time, IF(tim...

Mysql

2022-06-12

0 287