题解 | 下单复盘

with feb as (
    select * from orders
    where date_format(order_date, "%Y-%m") = '2024-02'
)
select c.customer_id, customer_name, 
    ifnull(count(distinct f.order_id), 0) as feb_2024_order_count,
    ifnull(sum(qty*price), 0) as feb_2024_total_amount, 
    round(ifnull(sum(qty*price) / ifnull(count(distinct f.order_id), 0), 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 c
left join feb f on c.customer_id = f.customer_id 
left join order_items o on f.order_id = o.order_id
group by c.customer_id, customer_name
order by feb_2024_total_amount desc, c.customer_id asc