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

--坑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