题解 | 下单复盘

select
    c.customer_id
    ,c.customer_name
    ,ifnull(count(distinct o.order_id), 0) as feb_2024_order_count
    ,round(ifnull(sum(qty*price), 0), 2) as feb_2024_total_amount
    ,case when ifnull(count(distinct o.order_id), 0) = 0 then 0.00
    else round(ifnull(sum(qty*price), 0)/count(distinct o.order_id), 2) 
    end 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 orders o on c.customer_id = o.customer_id and order_date between '2024-02-01' and '2024-02-29'
    left join order_items oi on o.order_id = oi.order_id
# where order_date between '2024-02-01' and '2024-02-29'
group by 1,2
order by feb_2024_total_amount desc, customer_id asc
;
# 如果保留无订单信息的用户，则日期限制需要在表连接时
# 如果用where限制，则不会保留