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

with a as (
        SELECT
        *
        from (
                SELECT 
                df1.order_id,df1.优惠前金额,
                df2.uid,df2.event_time,df2.total_amount,
                rank() OVER(PARTITION BY df2.uid ORDER BY df2.event_time asc) AS "rk"
                from (
                        SELECT 
                        order_id,
                        sum(price*cnt) as "优惠前金额"
                        from tb_order_detail 
                        group by order_id
                ) as df1
                left join tb_order_overall as df2
                on df1.order_id = df2.order_id
        ) as df 
        where rk = 1
        and date_format(event_time,'%Y-%m') = '2021-10'
)

SELECT 
round(sum(total_amount)/count(distinct order_id),1) as "avg_amount",
round(sum(优惠前金额-total_amount)/count(distinct order_id),1) as "avg_cost"
from a