题解 | 下单复盘

# 输出每位用户再2024年2月份的相关指标

with
t1 as(
    select
        customer_name,
        order_date,
        order_id,
        qty,
        price
    from
        orders
        left join customers using(customer_id)
        left join order_items using(order_id)
    where
        order_date between '2024-02-01' and '2024-02-28'
)
,
t2 as(
    select
        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
    from
        t1
    group by
        customer_name
)
,
t3 as(
    select
        customer_name,
        order_date,
        dense_rank()over(partition by customer_name order by order_date) as first_order_date,
        dense_rank()over(partition by customer_name order by order_date desc) as last_order_date
    from
        t1
)
,
t4 as(
    select distinct
        customer_name,
        order_date as feb_2024_first_order_date
    from
        t3
    where
        first_order_date=1
)
,
t5 as(
    select distinct
        customer_name,
        order_date as feb_2024_last_order_date
    from
        t3
    where
        last_order_date=1
)
,
t6 as(
    select
        customer_id,
        customer_name,
        if(feb_2024_order_count is null,0,feb_2024_order_count) as feb_2024_order_count,
        if(feb_2024_total_amount is null,0.00,feb_2024_total_amount) as feb_2024_total_amount,
        if(feb_2024_avg_order_amount is null,0.00,feb_2024_avg_order_amount) as feb_2024_avg_order_amount,
        feb_2024_first_order_date,
        feb_2024_last_order_date
    from
        customers
        left join t2 using(customer_name)
        left join t4 using(customer_name)
        left join t5 using(customer_name)
    order by
        feb_2024_total_amount desc,
        customer_id
)

select * from t6