题解 | 下单复盘

select customer_id,customer_name,count(distinct order_id) as feb_2024_order_count, 
ifnull(sum(price*qty),0) as feb_2024_total_amount,
round(ifnull(sum(price*qty)/count(distinct order_id),0),2) 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
left join 
(select * from orders 
where date_format(order_date,'%Y-%m') = '2024-02') a
using(customer_id)
left join order_items using(order_id)
group by customer_id,customer_name
order by feb_2024_total_amount desc,customer_id