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

WITH 
    new_uid AS(
        SELECT
            uid,
            MIN(event_time) min_dt
        FROM tb_order_overall
        GROUP BY uid
    ),
    10_user_info AS(
        SELECT uid,order_id,total_amount
        FROM tb_order_overall
        WHERE (uid,event_time)IN (
            SELECT * FROM new_uid
        ) AND DATE(event_time) LIKE "2021-10%"
    ),
    tb_avg_amount AS(
    SELECT 
        ROUND(
            SUM(total_amount)/COUNT(DISTINCT uid),
            1
        ) avg_amount
    FROM 10_user_info
    ),
    tb_avg_cost AS(
        SELECT
            ROUND(
                SUM(price*cnt)/COUNT(DISTINCT uid),
                1
            ) avg_price
        FROM 10_user_info
            INNER JOIN tb_order_detail USING(order_id)
    )

SELECT 
    avg_amount,
    avg_price - avg_amount avg_cost
FROM tb_avg_amount
    CROSS JOIN tb_avg_cost
    

这个查询的目标是计算2021年10月首次购买的用户的平均订单金额和平均成本，然后计算这两者之间的差异。通过多个CTE（Common Table Expressions）来逐步获取和处理数据，最终得出结果。以下是每个步骤的详细解释：

1. new_uid:从 tb_order_overall 表中获取每个用户的最早购买时间（min_dt）。
2. 10_user_info:从 tb_order_overall 表中选择在 new_uid 表中的用户，并且这些用户的最早购买时间在2021年10月。这里还获取了相关的订单ID和总金额。
3. tb_avg_amount:计算2021年10月首次购买的用户的平均订单金额。通过总金额的总和除以不同用户的数量来计算平均金额。
4. tb_avg_cost:计算2021年10月首次购买的用户的平均成本。通过每个订单的价格和数量的总和除以不同用户的数量来计算平均价格。
5. 最终查询:选择平均订单金额和平均成本，并计算两者之间的差异（avg_cost）。

解题思路总结：

1. 获取每个用户的最早购买时间：通过 new_uid CTE，获取每个用户的最早购买时间。
2. 筛选2021年10月首次购买的用户：通过 10_user_info CTE，筛选出这些用户在2021年10月的订单信息。
3. 计算平均订单金额：通过 tb_avg_amount CTE，计算这些用户的平均订单金额。
4. 计算平均成本：通过 tb_avg_cost CTE，计算这些用户的平均成本。
5. 计算差异：最后，通过 CROSS JOIN 将两个结果合并，并计算平均成本与平均订单金额之间的差异。