题解 | 下单复盘

select customer_id,customer_name,
count(distinct order_id) as feb_2024_order_count,coalesce(sum(qty*price),0.00) as feb_2024_total_amount,
coalesce(round(sum(qty*price) /count(distinct 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 ( select  o.order_id,price ,c.customer_id,customer_name,order_date,qty
 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 order_items oi on oi.order_id=o.order_id
) t
group by customer_id,customer_name
order by sum(qty*price) desc, customer_id;