题解 | 下单复盘

select c.customer_id,c.customer_name,

coalesce(cut.feb_2024_order_count,0) as feb_2024_order_count,

coalesce(cut.feb_2024_total_amount,0.00) as feb_2024_total_amount,

coalesce(round(cut.feb_2024_total_amount/cut.feb_2024_order_count,2),0.00) as feb_2024_avg_order_amount,

a1.order_date as feb_2024_first_order_date,

b1.order_date as feb_2024_last_order_date

from

customers c

left join (

    select o.customer_id,

    count(distinct o.order_id) as feb_2024_order_count,

    round(sum(oi.qty*oi.price),2) as feb_2024_total_amount

    from orders o left join order_items oi on o.order_id=oi.order_id

    where date_format(o.order_date,'%Y-%m')='2024-02'

    group by o.customer_id

) cut on c.customer_id=cut.customer_id

left join (

    select a.customer_id,a.order_date

    from

    (select customer_id,order_date,row_number() over(partition by customer_id order by order_date asc) as rnk

    from orders where date_format(order_date,'%Y-%m')='2024-02'

    ) a

    where a.rnk=1

) a1 on a1.customer_id=c.customer_id

left join (

    select b.customer_id,b.order_date

    from

    (select customer_id,order_date,row_number() over(partition by customer_id order by order_date desc) as rnk

    from orders where date_format(order_date,'%Y-%m')='2024-02'

    ) b

    where b.rnk=1

) b1 on b1.customer_id=c.customer_id

order by feb_2024_total_amount desc,customer_id;