牛客网 SQL17 10月的新户客单价和获客成本

描述

商品信息表tb_product_info

id	product_id	shop_id	tag	int_	quantity	release_time
1	8001	901	日用	60	1000	2020-01-01 10:00:00
2	8002	901	零食	140	500	2020-01-01 10:00:00
3	8003	901	零食	160	500	2020-01-01 10:00:00
4	8004	902	零食	130	500	2020-01-01 10:00:00

（product_id-商品ID, shop_id-店铺ID, tag-商品类别标签, in_price-进货价格, quantity-进货数量, release_time-上架时间）

订单总表tb_order_overall

id	order_id	uid	event_time	total_amount	total_cnt	status
1	301002	102	2021-10-01 11:00:00	235	2	1
2	301003	101	2021-10-02 10:00:00	300	2	1
3	301005	104	2021-10-03 10:00:00	160	1	1

（order_id-订单号, uid-用户ID, event_time-下单时间, total_amount-订单总金额, total_cnt-订单商品总件数, status-订单状态）

订单明细表tb_order_detail

id	order_id	product_id	price	cnt
1	301002	8001	85	1
2	301002	8003	180	1
3	301003	8004	140	1
4	301003	8003	180	1
5	301005	8003	180	1

（order_id-订单号, product_id-商品ID, price-商品单价, cnt-下单数量）

问题：请计算2021年10月商城里所有新用户的首单平均交易金额（客单价）和平均获客成本（保留一位小数）。

注：订单的优惠金额 = 订单明细里的{该订单各商品单价×数量之和} - 订单总表里的{订单总金额} 。

输出示例：

示例数据的输出结果如下

avg_amount	avg_cost
231.7	23.3

解释：

2021年10月有3个新用户，102的首单为301002，订单金额为235，商品总金额为85+180=265，优惠金额为30；

101的首单为301003，订单金额为300，商品总金额为140+180=320，优惠金额为20；

104的首单为301005，订单金额为160，商品总金额为180，优惠金额为20；

平均首单客单价为(235+300+160)/3=231.7，平均获客成本为(30+20+20)/3=23.3

题解：

步骤一：找到2021年10月新用户的首单交易（首单：交易时间最小min(date(event_time))，按照用户id，订单id,商品id分组查询（一个订单量可能会有好几个商品）

select uid,overall.order_id,detail.product_id,min(date(event_time)) dt,price,cnt,total_amount
from tb_order_detail detail
join tb_product_info product 
on product.product_id=detail.product_id
join tb_order_overall overall 
on detail.order_id=overall.order_id
where date_format(event_time,'%Y-%m')='2021-10'
group by uid,overall.order_id,detail.product_id,price,cnt,total_amount

 步骤二：查询出每个用户首单的平均订单金额和商品总金额（total_smount已经是总和了，所以直接查询出来）

		select uid,avg(total_amount) total_amount,sum(price*cnt) sum_amount -- 查询出每个用户首单的平均订单金额和商品总金额（total_smount已经是总和了，所以直接查询出来）
		from (
				-- 2021年10月新用户的首单交易（首单：交易时间最小），按照用户id，订单id,商品id查询
				select uid,overall.order_id,detail.product_id,min(date(event_time)) dt,price,cnt,total_amount
				from tb_order_detail detail
				join tb_product_info product 
				on product.product_id=detail.product_id
				join tb_order_overall overall 
				on detail.order_id=overall.order_id
				where date_format(event_time,'%Y-%m')='2021-10'
				group by uid,overall.order_id,detail.product_id,price,cnt,total_amount
		)t
		group by uid

步骤三： 计算2021年10月商城里所有新用户的首单平均交易金额（客单价）和平均获客成本（获客成本=price*cnt-实际总金额）

select round(AVG(total_amount),1) avg_amount,round(avg(sum_amount-total_amount),1)  avg_cost
from(
		select uid,avg(total_amount) total_amount,sum(price*cnt) sum_amount -- 查询出每个用户首单的平均订单金额和商品总金额（total_smount已经是总和了，所以直接查询出来）
		from (
				-- 2021年10月新用户的首单交易（首单：交易时间最小），按照用户id，订单id,商品id查询
				select uid,overall.order_id,detail.product_id,min(date(event_time)) dt,price,cnt,total_amount
				from tb_order_detail detail
				join tb_product_info product 
				on product.product_id=detail.product_id
				join tb_order_overall overall 
				on detail.order_id=overall.order_id
				where date_format(event_time,'%Y-%m')='2021-10'
				group by uid,overall.order_id,detail.product_id,price,cnt,total_amount
		)t
		group by uid
 )t