题解 | 下单复盘

select
customers.customer_id 
,customer_name 
,count(distinct orders.order_id) feb_2024_order_count
,ifnull(round(sum(qty*price),2),0) feb_2024_total_amount
,ifnull(round(ifnull(round(sum(qty*price),2),0)/count(distinct orders.order_id),2),0) feb_2024_avg_order_amount
,min(order_date) feb_2024_first_order_date
,max(order_date) feb_2024_last_order_date
from customers left join orders on customers.customer_id = orders.customer_id and month(order_date) = 2 
left join order_items oi on oi.order_id = orders.order_id
group by 1,2
order by 4 desc,1