荆天明201908291552707

题解

/ 注册

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

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

SELECT city, driver_id, avg_grade, avg_order_num, avg_mileage FROM ( SELECT tr.city, bo.driver_id, round( sum( bo.grade ) / count( bo.gr...

Mysql

2022-06-05

0 262

题解 | #牛客直播转换率#

select ct.course_id, ct.course_name, round((sum(bt.if_sign) * 100) / (sum(bt.if_vw)), 2) sign_rate from course_tb ct inner join behavior_tb bt on ct....

Mysql

2022-06-04

0 269

题解 | #牛客直播各科目同时在线人数#

select N.ci, N.cn, max(N.max_num) from ( select M.course_id ci, ct.course_name cn, sum(M.cou) over( PARTITION by...

Mysql

2022-06-04

0 240

题解 | #某乎问答高质量的回答中用户属于各级别的数量#

select (case when au.author_level = 1 or au.author_level = 2 then "1-2级" when au.author_level = 3 or au.author_level = 4 then "3-4级" when au.author_l...

Mysql

2022-06-04

0 219

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

select ab.course_id, ct.course_name, count(ab.user_id) online_num from attend_tb ab inner join course_tb ct on ab.course_id = ct.course_id where date_...

Mysql

2022-06-04

0 283

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

select product_id, round(sum(if_click)/count(1),3), round(case sum(if_click) when 0 then 1 else sum(if_cart)/sum(if_click) end,3), round(case sum(if_c...

Mysql

2022-06-03

0 210

题解 | #有取消订单记录的司机平均评分#

(select t.driver_id driver_id, round(sum(t.grade)/count(1), 1) avg_grade from tb_get_car_order as t right join (select driver_id from tb_get_car_orde...

Mysql

2022-06-02

0 338

题解 | #2021年国庆在北京接单3次及以上的司机统计信息#

select M.city, round(avg(num), 3), round(avg(fare), 3) from ( select tg.city, count(tr.order_id) num, sum(tr.fare) fare ...

Mysql

2022-06-02

0 237

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

(select tv.author, DATE_FORMAT(tu.start_time, "%Y-%m") month, round( sum( case when if_follow = 2 then -1 else if_foll...

Mysql

2022-05-29

0 205

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

SELECT M.tag, concat( cast( ( sum( realTime ) / sum( M.duration ) * 100 ) AS DECIMAL ( 10, 2 ) ), "%" ) as avg_play_progress FROM ( SELECT ...

Mysql

2022-05-22

0 205