--分解做法，容易看懂
with tiaojian as (
select
cs.customer_id,
cs.customer_name,
count(distinct os.order_id ) as feb_2024_order_count,
ifnull(sum(qty*price),0) as feb_2024_total_amount
,
ifnull(
round(
ifnull(sum(qty*price),0)/count(distinct os.order_id),2),0) as feb_2024_avg_order_amount
from  customers cs left join orders o on cs.customer_id=o.customer_id
                   left join order_items os on os.order_id=o.order_id
                   and
                   date_format(order_date,"%Y%m")=202402
                   group by cs.customer_id
),tiaojian1 as (
select 
customer_id,
min(order_date) as feb_2024_first_order_date,
max(order_date) as feb_2024_last_order_date
from orders
where
date_format(order_date,"%Y%m")=202402
group by customer_id
)


select 
t.customer_id,
t.customer_name,
t.feb_2024_order_count,
t.feb_2024_total_amount,
t.feb_2024_avg_order_amount,
t1.feb_2024_first_order_date,
t1.feb_2024_last_order_date
from tiaojian t left join tiaojian1 t1 
on t.customer_id=t1.customer_id
order by t.feb_2024_total_amount desc,t.customer_id asc