题解 | 下单复盘

select c.customer_id,customer_name,
count(t.order_id) as feb_2024_order_count,coalesce(sum(amount),0.00) as feb_2024_total_amount,
coalesce(round(sum(amount)/count( t.order_id),2),0.00) as  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 c 
left join orders o on o.customer_id=c.customer_id
and (order_date >= '2024-02-01' and order_date < '2024-03-01' )
left join  ( select  order_id,sum(qty*price) as amount 
from order_items
group by order_id) t on  t.order_id=o.order_id
group by customer_id,customer_name
order by sum(amount) desc, customer_id;