牛客666375956号

题解

/ 注册

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

题解 | #某乎问答单日回答问题数大于等于3个的所有用户#

SELECT answer_date,author_id,count(issue_id)as answer_cnt from answer_tb where DATE_FORMAT(answer_date,"%Y%m")="202111" group by answer_date,author_i...

Mysql

2022-02-18

0 262

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

SELECT case when author_level<=2 then "1-2级" when author_level>=3 and author_level<=4 then "3-4级" else "5-6级" end as level_cnt,count(*)as nu...

Mysql

2022-02-18

0 226

题解 | #某乎问答11月份日人均回答量#

SELECT answer_date,round(count(issue_id)/count(distinct author_id),2)as per_num from answer_tb where DATE_FORMAT(answer_date,"%Y-%m")="2021-11" GROUP ...

Mysql

2022-02-18

0 335

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

SELECT c.course_id,course_name,max_num FROM course_tb d,(SELECT course_id,max(num)as max_num from( select course_id, sum(diff) over (partition by cour...

Mysql

2022-02-17

0 369

题解 | #牛客直播各科目平均观看时长#

select d.course_id,course_name,bo as"attend_rate(%)" from course_tb d, (select a.course_id,round(count(distinct(case when timestampdiff(minute,in_date...

Mysql

2022-02-17

0 292

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

记得列名带%的要加引号 SELECT course_id,course_name,round(sum(if_sign)/ sum(if_vw)*100,2) as "sign_rate(%)" from course_tb inner join behavior_tb using(course_id...

Mysql

2022-02-17

5 737

题解 | #各城市最大同时等车人数#

1、根据打车时间、上车或者取消订单时间进行union 联立 2、按照city维度，order by dt,diff desc,对diff进行开窗统计 sum(diff) over(partition by city order by dt,diff desc) 3、最外层进行max聚合操作其中一...

Mysql

2022-02-17

0 259

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

select driver_id,round(avg(grade),1) from tb_get_car_order o left join tb_get_car_record r using(order_id) where driver_id in(select driver_id from tb...

Mysql

2022-02-16

0 274

题解 | #某店铺的各商品毛利率及店铺整体毛利率#

select "店铺汇总" as product_id, concat(round( (1 - SUM(in_price*cnt) / SUM(price*cnt))*100,1),"%") as profit_rate from tb_order_overall o join tb_produc...

Mysql

2022-02-15

0 307

题解 | #某店铺的各商品毛利率及店铺整体毛利率#

select "店铺汇总" as product_id, concat(round(100 * (1- SUM(in_price*cnt) / SUM(price*cnt)),1),"%") as profit_rate from tb_product_info t join tb_order_ov...

Mysql

2022-02-15

0 295