题解 | 下单复盘

/*分两步，先计算订单明细相关金额数量等，然后把结果左连接给客户表，否则GROUP BY 会报错*/

WITH

t1 AS (

SELECT

    a.order_id,

    a.customer_id,

    a.order_date,

    b.sku,

    b.qty * b.price AS je

FROM orders AS a

INNER JOIN order_items AS b ON a.order_id = b.order_id

    AND a.order_date >= '2024-02-01'

    AND a.order_date < '2024-03-01'

)

SELECT

    c.customer_id,

    c.customer_name,

    COALESCE(COUNT(DISTINCT t1.order_id),0) AS feb_2024_order_count,

    ROUND(COALESCE(SUM(t1.je),0),2) AS feb_2024_total_amount,

    ROUND(COALESCE(SUM(t1.je) / COUNT(DISTINCT t1.order_id),0),2) AS feb_2024_avg_order_amount,

    MIN(t1.order_date) AS feb_2024_first_order_date,

    MAX(t1.order_date) AS feb_2024_last_order_date

FROM customers AS c

LEFT JOIN t1 ON c.customer_id = t1.customer_id

GROUP BY c.customer_id,c.customer_name

ORDER BY feb_2024_total_amount DESC,c.customer_id;