会编程的发呆爱好者加麻加辣

分类

/ 注册

全部文章（共208篇）

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

#求每个城市中平均评分最高的司机 with t1 as( select city, driver_id, round(avg_grade,1) from( select *,dense_rank() over(partition by city order by avg_grade desc) a...

2025-04-24

0 25

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

#先找出有取消订单记录的司机 with a as( select distinct driver_id from tb_get_car_record tr join tb_get_car_order too on tr.order_id = too.order_id where date(too.o...

2025-04-24

0 33

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

select city, round(avg(cnt),3) as avg_order_num, avg(income) as avg_income from( select tr.city,too.driver_id, count(too.order_id) as cnt, sum(too.far...

2025-04-24

0 26

题解 | 店铺901国庆期间的7日动销率和滞销率

#t1查询10-1至10-3有交易记录的日期 with t1 as( select distinct date(event_time) as dt from tb_order_overall where date(event_time) between '2021-10-01...

2025-04-23

0 24

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

#订单明细中的该订单各商品单价×数量之和 with a as( select order_id, price*cnt as money from tb_order_detail) #新用户 select round(avg(user_amount),1) as avg_amount, round(...

2025-04-22

0 16

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

#近90天各商品各用户重复购买次数（大于1次） with t as( select product_id,count(case when num>1 then uid else null end) as cnt from( select td.product_id,uid, count...

2025-04-21

0 28

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

#总店铺 select '店铺汇总' as product_id, concat(round((1-sum(tp.in_price*td.cnt)/sum(td.price*td.cnt))*100,1),'%') as profit_rate from tb_order_detail td joi...

2025-04-21

0 25

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

select product_id, round(avg(if_click),3) as ctr, round(sum(if_cart)/sum(if_click),3) as cart_rate, round(sum(if_payment)/sum(if_cart),3) as payment_r...

2025-04-20

0 28

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

select * from( select date_format(event_time,'%Y-%m') as month, sum(total_amount) as GMV_month from tb_order_overall where year(event_time)=2021 and s...

2025-04-20

0 24

题解 | 连续签到领金币

#取出每个用户连续登录情况 with a as(select uid,date(in_time) as dt, row_number() over(partition by uid order by date(in_time)) as rk, date_sub(date(in_time),inter...

2025-04-19

0 24