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

WITH t AS (    
    SELECT
        t3.order_id,
        AVG(total_amount) AS sum_amount,
        SUM(price * cnt) - AVG(total_amount) AS cost
    FROM tb_order_detail t3
    JOIN tb_order_overall t2 ON t3.order_id = t2.order_id
    JOIN (
        SELECT uid, MIN(event_time) first_time 
        FROM tb_order_overall
        GROUP BY uid
    )t1 ON t2.event_time = t1.first_time
    WHERE DATE_FORMAT(event_time, '%Y-%m') = '2021-10'
    GROUP BY t3.order_id
)

SELECT 
    ROUND(AVG(sum_amount), 1) AS avg_amount,
    ROUND(AVG(cost), 1) AS avg_cost
FROM t

有两个地方是关键，一是在使用了GROUP BY的查询中不能直接查询没有聚合的列total_amount，所以在这里加上一个AVG把它包起来就可以了。二是如何判断“用户的首次购买”，这里采取的方法是连接一个查询“每个用户最小时间”的子查询表，连接条件是购买时间 = 最小时间。