题解 | 下单复盘

SELECT
	c.customer_id,
	customer_name,
	count( o.customer_id ) AS feb_2024_order_count,
	COALESCE ( sum( cnt ), 0 ) AS feb_2024_total_amount,
	round( COALESCE ( sum( cnt )/ count( o.customer_id ), 0 ), 2 ) AS feb_2024_avg_order_amount,
	min( order_date ) AS feb_2024_first_order_date,
	max( order_date ) AS feb_2024_last_order_date 
FROM
	customers c
	LEFT JOIN ( SELECT * FROM orders WHERE order_date LIKE '2024-02%' ) o ON c.customer_id = o.customer_id
	LEFT JOIN ( SELECT order_id, sum( qty * price ) AS cnt FROM order_items oi GROUP BY 1 ) t ON o.order_id = t.order_id 
GROUP BY
	1 
ORDER BY
	4 DESC,1