题解 | 下单复盘

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