题解 | #10月的新户客单价和获客成本#
10月的新户客单价和获客成本
https://www.nowcoder.com/practice/d15ee0798e884f829ae8bd27e10f0d64
--坑1:order总表 left join order 明细表,会出现total_amount翻倍的情况(出现笛卡尔积) --解法1:15行:min(total_amount)AS raw_price --坑2:逻辑是先找到首单,再找到是21年10月份的时间 --解法:先用row_number找到首单,再嵌套month(event_time)=10 SELECT ROUND(SUM(now_practice)/COUNT(DISTINCT uid),1) AS avg_amount ,ROUND(SUM(raw_practice-now_practice)/COUNT(distinct uid),1)AS avg_cost FROM( SELECT uid ,sum(raw_price) AS now_practice ,sum(now_price) AS raw_practice FROM( SELECT uid ,event_time ,row_number()OVER (PARTITION BY uid order by event_time asc)AS rnk ,min(total_amount)AS raw_price ,sum(price*cnt)AS now_price FROM tb_order_overall AS a LEFT JOIN tb_order_detail AS b ON a.order_id=b.order_id GROUP BY uid,event_time )AS a WHERE rnk=1 AND month(event_time)=10 AND year(event_time)=2021 GROUP BY uid )AS b