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

SELECT
	ROUND(SUM(total_amount) / COUNT(t1.uid), 1) avg_amount,
	ROUND(SUM(total_price - total_amount) / COUNT(t1.uid), 1) avg_cost
FROM
	(
		SELECT
			a.*,
			b.total_price,
			row_number() over(partition BY uid order by event_time) rn
		FROM
			tb_order_overall a
		LEFT JOIN
			(
				SELECT
					order_id,
					SUM(price * cnt) total_price
				FROM
					tb_order_detail
				GROUP BY
					order_id
			)
			b
		ON
			a.order_id = b.order_id
	)
	t1
INNER JOIN
	(
		SELECT
			uid,
			MIN(event_time) first_event_tm
		FROM
			tb_order_overall
		GROUP BY
			uid
		HAVING
			SUBSTR(first_event_tm, 1, 7) = '2021-10'
	)
	t2
ON
	t1.uid = t2.uid
	AND t1.event_time = t2.first_event_tm