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

1. 先找到10月的新客户及其所下订单, 这里用的窗口函数，用group by的话要麻烦一点

select 
	DISTINCT uid, 
    first_value(order_id) over (partition by uid order by event_time) as order_id,
    first_value(event_time) over (partition by uid order by event_time) as first_date,
    first_value(total_amount) over (partition by uid order by event_time) as first_order_amount
    from tb_order_overall
    where status = 1

2. 将10月新客户所下订单与 tb_order_detail表单关联获取对应订单产品的详情，使用用来计算每笔订单的优惠金额

select uid,
	avg(first_order_amount) as first_order_amount, 
    sum(price)-avg(first_order_amount) as cost 
    from (
        select uid,order_id,first_order_amount,product_id,price
        from (
        select DISTINCT uid, 
            first_value(order_id) over (partition by uid order by event_time) as order_id,
            first_value(event_time) over (partition by uid order by event_time) as first_date,
            first_value(total_amount) over (partition by uid order by event_time) as first_order_amount
            from tb_order_overall
            where status = 1
        ) temp 
        inner join tb_order_detail 
        using(order_id)
        where date_format(first_date, '%Y-%m') ='2021-10'
    ) temp2
    GROUP BY uid

3. 求平均数计算10月的新客户单价及获客成本

select 
    round(avg(first_order_amount),1) as avg_amount, 
    round(avg(cost),1) as avg_cost from (
    select uid,
      avg(first_order_amount) as first_order_amount, 
      sum(price)-avg(first_order_amount) as cost 
      from (
        select uid,order_id,first_order_amount,product_id,price
        from (
        select DISTINCT uid, 
            first_value(order_id) over (partition by uid order by event_time) as order_id,
            first_value(event_time) over (partition by uid order by event_time) as first_date,
            first_value(total_amount) over (partition by uid order by event_time) as first_order_amount
            from tb_order_overall
            where status = 1
        ) temp 
        inner join tb_order_detail 
        using(order_id)
        where date_format(first_date, '%Y-%m') ='2021-10'
    ) temp2
    GROUP BY uid
 ) temp3