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

WITH T1 AS (
    SELECT
    *,
    row_number() over(partition by uid order by event_time) rn
    FROM
    tb_order_overall
),
T2 AS (
    SELECT
    C.order_id,
    price,
    cnt,
    total_amount
    FROM
    tb_order_detail C
    JOIN tb_product_info A USING(product_id)
    JOIN tb_order_overall B USING(order_id)
    WHERE 
    order_id IN (
        SELECT order_id
        FROM T1
        WHERE rn = 1
    )
    AND left(event_time,7) = '2021-10'
)
SELECT
    round(avg(total_amount),1) avg_amount,
    round(avg(cost),1) avg_cost
FROM
(
    SELECT
    order_id,
    max(total_amount) total_amount,
    sum(price*cnt)-max(total_amount) cost
    FROM T2
    GROUP BY order_id
) t