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

WITH 
    first_completed_orders AS (
        SELECT
            uid,
            order_id,
            total_amount
        FROM
            (
                SELECT
                    uid,
                    order_id,
                    event_time,
                    total_amount,
                    total_cnt,
                    status,
                    RANK() OVER (
                        PARTITION BY
                            uid
                        ORDER BY
                            event_time ASC
                    ) AS order_sequence
                FROM
                    tb_order_overall
                WHERE
                    status = 1  -- 假设1表示已完成订单
            ) AS ranked_orders
        WHERE
            order_sequence = 1  -- 每个用户的第一笔订单
            AND YEAR(event_time) = 2021
            AND MONTH(event_time) = 10
    ),
    original_order_values AS (
        SELECT
            order_id,
            SUM(price * cnt) AS original_total_value
        FROM
            tb_order_detail
            JOIN tb_product_info USING (product_id)
        GROUP BY
            order_id
    )
SELECT
    ROUND(AVG(total_amount), 1) AS avg_first_order_amount,
    ROUND(AVG((original_total_value - total_amount)), 1) AS avg_discount_saving
FROM
    original_order_values
    JOIN first_completed_orders USING (order_id)