阿翟啊

题解

/ 注册

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

题解 | #零食类商品中复购率top3高的商品#

select a.product_id, ifnull(round(cnt_2/cnt_total,3),0.000) repurchase_rate from tb_product_info a left join (select product_id, ...

Mysql

2021-12-01

6 518

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

select '店铺汇总' as product_id, concat(round((1-sum(cnt*in_price)/sum(cnt*price))*100,1),'%') profit_rate from tb_order_detail a left join tb_prod...

Mysql

2021-12-01

0 350

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

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

Mysql

2021-12-01

15 2466

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

SELECT date_format(event_time,'%Y-%m') `month`, sum(total_amount) GMV from tb_order_overall where (status != 2) and year(event_time) = 2021 gro...

Mysql

2021-12-01

13 3087

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

select a.video_id, round((100*fin+5*good+3*comm+2*ret)/(fresh+1),0) hot_index from (select a.video_id, count(if(TIMESTAMPDIFF(SECOND,star...

Mysql

2021-12-01

0 402

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

select city, driver_id, avg_grade, avg_order_num, avg_mileage from(select city, driver_id, round...

Mysql

2021-11-30

0 392

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

sselect ifnull(driver_id,'总体') driver_id, round(avg(grade),1) avg_grade from tb_get_car_order a left join tb_get_car_record b on a.order_id = b...

Mysql

2021-11-30

5 842

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

select city, round(avg(cnt),3) avg_order_num, round(avg(total_fare),3) avg_income from (select driver_id, city, ...

Mysql

2021-11-30

0 351

题解 | #某宝店铺连续2天及以上购物的用户及其对应的天数#

select user_id , count(*) days_count from (select user_id, sales_date, ROW_NUMBER() over (partition by user_id order by sales_date...

Mysql

2021-11-30

9 1826

题解 | #某宝店铺折扣率#

select round(sum(a.sales_price)/sum(b.tag_price*a.sales_num)*100,2) `discount_rate(%)` from sales_tb a left join product_tb b on a.item_id = b.item_id

Mysql

2021-11-30

0 335