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

select round(avg(total_amount),1) as avg_amount
        ,round(avg(raw_amount-total_amount),1) as avg_cost
from(
    select uid,sum(price*cnt) as raw_amount
        ,max(total_amount) total_amount
    from(
        select uid,
                date(event_time) event_day,
                total_amount,price,cnt,
                rank() over(partition by uid order by event_time) as irank
        from tb_order_overall t2
        left join tb_order_detail t3
        on t2.order_id = t3.order_id
        )as t1
    where irank = 1 and event_day between '2021-10-01' and '2021-10-31'
    group by uid
    )as t2

一个很简洁的思路：

step1：连接后面两个表，用窗口函数统计出每个用户的首次下单时间

rank() over(partition by uid order by event_time) as irank

注意：为什么不能用row_number()???因为后面两个表被连接，event_time被广播，下一步我要通过irank=1且event_time在2021年10月份来筛选用户，用row_number不能筛选出首单的全部明细！！！

select uid,

date(event_time) event_day,

total_amount,price,cnt,

rank() over(partition by uid order by event_time) as irank

from tb_order_overall t2

left join tb_order_detail t3

on t2.order_id = t3.order_id

step2：筛选出irank=1且event_time在2021年10月份的信息，并聚合求出实际付款金额和原总金额

select uid,sum(price*cnt) as raw_amount

,max(total_amount) total_amount

from(

select uid,

date(event_time) event_day,

total_amount,price,cnt,

rank() over(partition by uid order by event_time) as irank

from tb_order_overall t2

left join tb_order_detail t3

on t2.order_id = t3.order_id

)as t1

where irank = 1 and event_day between '2021-10-01' and '2021-10-31'

group by uid

step3：直接在上表聚合求解即可。

select round(avg(total_amount),1) as avg_amount

,round(avg(raw_amount-total_amount),1) as avg_cost

from(

select uid,sum(price*cnt) as raw_amount

,max(total_amount) total_amount

from(

select uid,

date(event_time) event_day,

total_amount,price,cnt,

rank() over(partition by uid order by event_time) as irank

from tb_order_overall t2

left join tb_order_detail t3

on t2.order_id = t3.order_id

)as t1

where irank = 1 and event_day between '2021-10-01' and '2021-10-31'

group by uid

)as t2