题解 | 下单复盘

with feb as (
    select o.order_id,customer_id,order_date,sku,qty,price
    from order_items oi join orders o on oi.order_id = o.order_id
    where order_date like '2024-02%'
),
cust_order_date as (
    select c.customer_id,min(f.order_date) as feb_2024_first_order_date,
    max(f.order_date) as feb_2024_last_order_date
    from customers c left join feb f on c.customer_id = f.customer_id
    group by c.customer_id
),
cust_stats as (
    select c.customer_id,customer_name,count(distinct order_id) feb_2024_order_count,
    ifnull(round(sum(price*qty),2),0) feb_2024_total_amount,
    ifnull(round(sum(price*qty)/count(distinct order_id),2),0) feb_2024_avg_order_amount
    from customers c left join feb f on c.customer_id = f.customer_id
    group by c.customer_id
)
select cs.customer_id,customer_name,feb_2024_order_count,feb_2024_total_amount,feb_2024_avg_order_amount,feb_2024_first_order_date,feb_2024_last_order_date
from cust_stats cs left join cust_order_date cod on cs.customer_id = cod.customer_id
order by cs.feb_2024_total_amount desc,cs.customer_id