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

SELECT ROUND(AVG(total_amount), 1) AS avg_amount,
    ROUND(AVG(cost), 1) AS avg_cost
FROM (
    SELECT uid, order_id, total_amount,
        (SUM(price * cnt) - total_amount) AS cost
    FROM (
        SELECT uid, order_id, total_amount, status, event_time,
            ROW_NUMBER() OVER(PARTITION BY uid ORDER BY event_time) AS first_rank
        FROM tb_order_overall
    ) first_purchase_t
    JOIN tb_order_detail
    USING(order_id)
    WHERE first_rank = 1 
        AND DATE_FORMAT(event_time, '%Y-%m') = '2021-10'
        AND status = 1
    GROUP BY uid, order_id, total_amount
) amount_t