题解 | 下单复盘

select
    c.customer_id,
    c.customer_name,
    coalesce(count(distinct o.order_id),0) as feb_2024_order_count,
    coalesce(sum(i.qty*i.price),0) as feb_2024_total_amount,
    coalesce(round(sum(i.qty*i.price)/count(distinct o.order_id),2),0) as feb_2024_avg_order_amount,
    coalesce(min(o.order_date),null) as feb_2024_first_order_date,
    coalesce(max(o.order_date),null) as feb_2024_last_order_date
from customers c
left join orders o on c.customer_id=o.customer_id and left(o.order_date,7)='2024-02'
left join order_items i on o.order_id=i.order_id
group by c.customer_id,c.customer_name
order by feb_2024_total_amount desc,c.customer_id