题解 | 下单复盘

SELECT
    c.customer_id,
    c.customer_name,
    IFNULL(COUNT(DISTINCT o.order_id), 0) AS feb_2024_order_count,
    ROUND(IFNULL(SUM(oi.qty * oi.price), 0) ,2)AS feb_2024_total_amount, 
    ROUND(
        IFNULL(
            SUM(oi.qty * oi.price) / NULLIF(COUNT(DISTINCT o.order_id), 0),
            0
        ),
        2
    ) AS feb_2024_avg_order_amount, 
    MIN(o.order_date) AS feb_2024_first_order_date,
    MAX(o.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-29' 
    LEFT JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY
    c.customer_id,
    c.customer_name
ORDER BY
feb_2024_total_amount DESC,customer_id ASC;