题解 | 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
    FROM tb_order_detail a
    LEFT JOIN tb_order_overall b ON a.order_id = b.order_id
    LEFT JOIN tb_product_info c ON a.product_id = c.product_id
    WHERE status = 1
),
t2 AS (
    SELECT uid,
           min(event_time) as min_time
    FROM t1
    GROUP BY uid
    having DATE(min_time) BETWEEN '2021-10-01' AND '2021-10-31'
),
t3 AS (
    SELECT t1.uid,
           t1.less_amount,
           t1.total_amount,
           t1.event_time,
           t2.min_time
    FROM t1
    LEFT JOIN t2 ON t1.uid = t2.uid
    HAVING t1.event_time = t2.min_time
),
t4 AS (SELECT uid,
              max(total_amount) AS max_total_amount,
              sum(less_amount) - max(total_amount) AS profit
       FROM t3
       GROUP BY uid
),
t5 AS (
    SELECT ROUND(avg(max_total_amount),1) AS avg_amount,
           ROUND(avg(profit),1)     AS avg_cost
    FROM t4
)
SELECT * FROM t5;

# 这道题目的关键在于订单总价（即total_amount）的平均值要找对！