题解 | 下单复盘

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