题解 | 下单复盘

-- 计算二月份每一个订单的总金额
with temp as (
    select
        o.customer_id,
        o.order_id,
        o.order_date,
        sum(oi.qty * oi.price) as order_amount
        from orders o
        left join order_items oi
        on oi.order_id = o.order_id
        where o.order_date like '%2024-02%'
        GROUP BY 
        o.customer_id,
        o.order_id,
        o.order_date
)
    select
        c.customer_id,
        c.customer_name,
        count(distinct tp.order_id) as feb_2024_order_count,
        coalesce(round(sum(tp.order_amount),2),0.00) as feb_2024_total_amount,
IF(
        COUNT(DISTINCT tp.order_id) = 0, 
        0.00, 
        ROUND(SUM(tp.order_amount) / COUNT(DISTINCT tp.order_id), 2)
    ) AS feb_2024_avg_order_amount,
        min(tp.order_date) as feb_2024_first_order_date,
        max(tp.order_date) as feb_2024_last_order_date
        from 
        customers c 
        left join temp tp 
        on tp.customer_id = c.customer_id
        group by customer_id,customer_name
        order by feb_2024_total_amount DESC, 
    c.customer_id ASC;