讨厌吃香菜的无尾熊刷了100道题

题解

/ 注册

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

题解 | #10月的新户客单价和获客成本#

select round(avg(t1.total_amount),1) avg_amount, round(avg(t1.total_price - t1.total_amount),1) avg_cost from ( select t_o.uid, t_o.total_amount, t_o....

Mysql

2021-12-14

0 282

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

select '店铺汇总' product_id, concat(round((1 - sum(cntin_price)/sum(cntprice))*100,1),'%') profit_rate from tb_order_detail t_d join tb_product_info t_i ...

Mysql

2021-12-11

0 295

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

select product_id, round(sum(if_click) / count(*),3) ctr, round(sum(if_cart) / sum(if_click),3) cart_rate, round(sum(if_payment) / sum(if_cart),3) pay...

Mysql

2021-12-10

0 233

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

select t1.date1, sum(if(t1.total_amount > 0,t1.total_amount,0)) GMV from ( select date_format(tb.event_time,'%Y-%m') date1, total_amount from tb_or...

Mysql

2021-12-10

0 299

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

select t1.auth author, t1.da, round(t1.retweet_cnt/cnt,3), sum(t1.retweet_cnt) over(partition by t1.auth order by t1.da) total_fans from ( select ti.a...

Mysql

2021-12-09

0 214

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

select ti.tag, sum(if(if_retweet = 1,1,0)) retweet_cnt, round(sum(if(if_retweet = 1,1,0))/count(*),3) retweet_rate from tb_user_video_log tl join tb_v...

Mysql

2021-12-09

0 209

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

select t2.tag, concat(t2.avg_p,'%') from ( select ...

Mysql

2021-12-08

0 250

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

select t2.video_id, round(t2.s/t2.ss,3) avg_rate from ( select t1.video_id, sum(if(t1.diff >= t1.duration,1,0)) s, count(*) ss from (select tu.uid,...

Mysql

2021-12-08

0 232