李齐亮

分类

/ 注册

全部文章（共31篇）

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

WITH t1 AS ( SELECT b.city, a.driver_id, ROUND(AVG(grade), 1) AS avg_grade FROM tb...

2024-12-18

0 48

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

WITH t1 AS ( -- 1、先找出谁是被取消的司机 SELECT driver_id FROM tb_get_car_order WHERE DATE(order_time) BETWEEN '2021-10-01' AND '2021-10-...

2024-12-18

0 34

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

WITH t1 AS ( SELECT order_id, driver_id, IFNULL(fare,0) AS fare FROM tb_get_car_order WHERE DATE(order_time) BETWE...

2024-12-18

0 35

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

WITH t1 AS ( -- 公共宽表 SELECT a.order_id, a.product_id, a.cnt, b.uid, a.price * a.cnt AS less_amount, ...

2024-12-17

0 50

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

WITH t1 AS ( SELECT a.order_id, b.uid, a.price * a.cnt AS less_amount, b.event_time, b.total_amount FR...

2024-12-17

0 40

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

WITH t1 AS ( SELECT a.product_id, a.cnt, b.uid, DATE(b.event_time) AS dt FROM tb_order_detail a LEFT JOIN ...

2024-12-16

0 42

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

WITH t_1 AS ( SELECT a.product_id, a.price, a.cnt, c.shop_id, c.in_price FROM tb_order_detail a IN...

2024-12-16

0 83

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

WITH t1 AS ( SELECT product_id, count(product_id) AS show_num, sum(if_click) AS click_num, sum(if_cart) AS cart_nu...

2024-12-16

0 43

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

WITH t1 AS ( SELECT SUBSTR(event_time,1,7) AS month, total_amount FROM tb_order_overall WHERE status != 2 AND substr(event...

2024-12-16

0 48

题解 | SQL 167. 连续签到领金币

WITH t1 AS( -- t1表筛选出活动期间内的数据，并且为了防止一天有多次签到活动，distinct 去重 SELECT DISTINCT uid, DATE(in_time) dt, DENSE_RANK() over(PARTITION BY uid ORDER BY DA...

2024-12-16

0 44