题解 | 下单复盘

WITH t1 AS (
		SELECT c.customer_id,
			   c.customer_name,
			   o.order_id,
			   o.order_date,
			   oi.sku,
			   oi.qty,
			   oi.price
		FROM customers c 
		LEFT JOIN orders o ON o.customer_id = c.customer_id AND MONTH(order_date) = 2
		LEFT JOIN order_items oi ON oi.order_id  = o.order_id
),
-- 2月订单数 
t2 AS (
		SELECT customer_id,customer_name,COUNT(DISTINCT order_id) AS feb_2024_order_count
		FROM t1
		GROUP BY customer_id,customer_name
),
-- 2月订单总金额 
t3 AS (
		SELECT customer_id,SUM(qty * price) AS feb_2024_total_amount
		FROM t1
		GROUP BY customer_id
),
-- 2月首次/末次下单日期 
t4 AS(
		SELECT DISTINCT customer_id,
			   FIRST_VALUE(order_date)OVER(PARTITION BY customer_id) AS feb_2024_last_order_date,
			   LAST_VALUE(order_date)OVER(PARTITION BY customer_id) AS feb_2024_first_order_date
		FROM t1
)
SELECT t2.customer_id,
	   t2.customer_name,
	   t2.feb_2024_order_count,
	   COALESCE(t3.feb_2024_total_amount,0) AS feb_2024_total_amount,
	   CASE
			WHEN t2.feb_2024_order_count = 0 THEN 0.00
			WHEN t2.feb_2024_order_count != 0 THEN ROUND(t3.feb_2024_total_amount/t2.feb_2024_order_count,2) 
		END AS feb_2024_avg_order_amount,
		t4.feb_2024_first_order_date,
		t4.feb_2024_last_order_date
FROM t2 
LEFT JOIN t3 ON t3.customer_id = t2.customer_id
LEFT JOIN t4 ON t4.customer_id = t2.customer_id
ORDER BY feb_2024_total_amount DESC,t2.customer_id ASC