题解 | 下单复盘

SELECT c.customer_id,
       customer_name,
       COUNT(DISTINCT o.order_id) AS feb_2024_order_count,
       ROUND(IFNULL(SUM(qty*price),0),2) AS feb_2024_total_amount,
       ROUND(IFNULL(SUM(qty*price)/COUNT(DISTINCT o.order_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 orders o ON c.customer_id=o.customer_id AND (o.order_date BETWEEN '2024-02-01' AND '2024-02-28')
LEFT JOIN order_items o1 ON o.order_id=o1.order_id 
GROUP BY c.customer_id,
         customer_name
ORDER BY feb_2024_total_amount DESC,c.customer_id ASC