题解 | 下单复盘

SELECT
    c.customer_id,
    c.customer_name,
    COUNT(DISTINCT o.order_id) AS feb_2024_order_count,
    COALESCE(SUM(ot.qty * ot.price), 0) AS feb_2024_total_amount,
    round(COALESCE(SUM(ot.qty * ot.price)/count(distinct o.order_id), 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 >= '2024-02-01'
    AND o.order_date < '2024-03-01'
    LEFT JOIN order_items ot ON ot.order_id = o.order_id
GROUP BY
    c.customer_id,
    c.customer_name
order by feb_2024_total_amount desc, customer_id asc;