题目：

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

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

解题思路

步骤 1：先找出10月份的新用户，再找出其对应的第一笔订单

• 21年10月份的新用户，可以理解为：首单发生在2021年10月的用户。查询语句为：

SELECT uid, MIN(event_time) AS first_dt /* 最小的订单日期即为首单日期 */
FROM tb_order_overall
WHERE status = 1 /* 确保是已完成的订单 */
GROUP BY uid
HAVING MONTH(first_dt) = 10 AND YEAR(first_dt) = 2021 /* 限定首单年月为2021年10月 */

步骤 2：找出新用户首单所对应的交易金额

• 步骤1的查询结果与表tb_order_overall表联结即可

SELECT t1.order_id, t1.total_amount
FROM
tb_order_overall AS t1
INNER JOIN

/* 步骤1的查询结果 */
(SELECT uid, MIN(event_time) AS first_dt
FROM tb_order_overall
WHERE status = 1
GROUP BY uid
HAVING MONTH(first_dt) = 10 AND YEAR(first_dt) = 2021) AS t2

ON t1.uid = t2.uid AND t1.event_time = t2.first_dt /* 只取10月份新用户及其首单 */
WHERE status = 1

步骤 3：再找出各新用户的首单，在订单明细里的{各商品单价×数量之和}，以及最终的优惠金额

• 先从表tb_order_detail中找出所有订单的{各商品单价×数量之和}

SELECT order_id, SUM(price * cnt) AS old_amount /* {各商品单价×数量之和} */
FROM tb_order_detail
GROUP BY order_id

• 其次，将上述查询结果与步骤2的查询结果进行联结，即可计算最终的优惠金额

SELECT ROUND(AVG(t3.total_amount), 1) AS avg_aomunt, /* 首***均交易金额（客单价) */
       ROUND(AVG(t4.old_amount - t3.total_amount), 1) AS avg_cost /* 首***均优惠金额（获客成本） */
FROM

	/* 步骤2的查询结果 */
    (SELECT t1.order_id, t1.total_amount
    FROM
    tb_order_overall AS t1
    INNER JOIN
    (SELECT uid, MIN(event_time) AS first_dt
    FROM tb_order_overall
    WHERE status = 1
    GROUP BY uid
    HAVING MONTH(first_dt) = 10 AND YEAR(first_dt) = 2021) AS t2
    ON t1.uid = t2.uid AND t1.event_time = t2.first_dt /* 只取10月份新用户及其首单 */
    WHERE status = 1) AS t3
    
LEFT OUTER JOIN

	/* 步骤3.1的查询结果 */
     (SELECT order_id, SUM(price * cnt) AS old_amount
     FROM tb_order_detail
     GROUP BY order_id) AS t4
     
ON t3.order_id = t4.order_id;