题解 | 下单复盘

with a as(select o.customer_id,
    count(distinct o.order_id) co,
    round(sum(qty*price),2) rs,
    round(sum(qty*price)/count(distinct o.order_id),2) rsc,
    min(order_date) feb_2024_first_order_date,
    max(order_date) feb_2024_last_order_date
from orders o 
inner join order_items oi 
on o.order_id=oi.order_id
where date_format(order_date,'%Y%m')=202402
group by o.customer_id) 

select c.customer_id,
    customer_name,
    ifnull(co,0) feb_2024_order_count,
    ifnull(rs,0) feb_2024_total_amount,
    ifnull(rsc,0.00) feb_2024_avg_order_amount,
    feb_2024_first_order_date,
    feb_2024_last_order_date
from customers c 
left join a 
on a.customer_id=c.customer_id
order by feb_2024_total_amount desc,c.customer_id asc