题解 | 下单复盘

with
    lsb1 as (
        select
            c.customer_id,
            c.customer_name,
            sum(
                case
                    when t.order_id is null then 0
                    else 1
                end
            ) as feb_2024_order_count,
            min(t.order_date) as feb_2024_first_order_date,
            max(t.order_date) as feb_2024_last_order_date
        from
            customers c
            left join (
                select
                    *
                from
                    orders
                where
                    date_format(order_date, '%Y-%m') = '2024-02'
            ) t on c.customer_id = t.customer_id
        group by
            c.customer_id,
            c.customer_name
    ),
    lsb2 as (
        select
            o.customer_id,
            sum(oi.qty * oi.price) as feb_2024_total_amount
        from
            orders o
            join order_items oi on o.order_id = oi.order_id
        where
            date_format(o.order_date, '%Y-%m') = '2024-02'
        group by
            o.customer_id
    )
select
    l1.customer_id, 
    l1.customer_name, 
    l1.feb_2024_order_count, 
    round((case when l2.feb_2024_total_amount is null then 0 else l2.feb_2024_total_amount end), 2) as feb_2024_total_amount, 
    round((case when l2.feb_2024_total_amount is null then 0 else l2.feb_2024_total_amount/l1.feb_2024_order_count end), 2) as feb_2024_avg_order_amount,
    l1.feb_2024_first_order_date, 
    l1.feb_2024_last_order_date
from
    lsb1 l1
    left join lsb2 l2 on l1.customer_id = l2.customer_id
order by feb_2024_total_amount desc, l1.customer_id asc