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

WITH a AS(
    SELECT too.uid, tod.order_id, DATE(too.event_time) date, 
        DENSE_RANK() OVER(PARTITION BY too.uid ORDER BY too.event_time) rk,
        too.total_amount, tod.price, tod.cnt
    FROM tb_order_overall too 
    JOIN tb_order_detail tod USING(order_id)
    WHERE uid IN(
        SELECT uid  -- 获取新用户id
        FROM tb_order_overall
        GROUP BY uid
        HAVING MIN(DATE(event_time))  BETWEEN '2021-10-01' AND '2021-10-31'
    ) AND too.status = 1 AND DATE(too.event_time) BETWEEN '2021-10-01' AND '2021-10-31'
),
b AS(
    SELECT uid, total_amount, MAX(price*cnt) yuanjia, SUM(price*cnt)-total_amount  youhui
    FROM a 
    WHERE rk = 1
    GROUP BY uid, total_amount
)
 SELECT ROUND(AVG(total_amount),1) avg_amount,ROUND(AVG(youhui),1) avg_cost
 FROM b