万南天 · 数据分析作品集

with payment_agg as (
    select
        order_id,
        sum(payment_value) as payment_value
    from olist_order_payments_dataset
    group by order_id
),

     trade_kpi as (
         select
             round(sum(p.payment_value), 2) as gmv,
             count(distinct o.order_id) as valid_order_cnt,
             round(sum(p.payment_value) / count(distinct o.order_id), 2) as aov
         from olist_orders_dataset o
                  join payment_agg p
                       on o.order_id = p.order_id
         where o.order_status = 'delivered'
     ),

     customer_kpi as (
         select
             count(distinct c.customer_unique_id) as unique_customer_cnt
         from olist_orders_dataset o
                  join olist_customers_dataset c
                       on o.customer_id = c.customer_id
         where o.order_status = 'delivered'
     ),

     fail_kpi as (
         select
             round(
                     sum(order_status in ('canceled', 'unavailable')) / count(*) * 100,
                     2
             ) as fail_rate_pct
         from olist_orders_dataset
         where order_status in ('delivered', 'canceled', 'unavailable')
     ),

     delivery_kpi as (
         select
             round(avg(datediff(order_delivered_customer_date, order_purchase_timestamp)), 2) as avg_delivery_days
         from olist_orders_dataset
         where order_status = 'delivered'
           and order_delivered_customer_date is not null
           and order_purchase_timestamp is not null
     )

select
    t.gmv as '成交总额',
    t.valid_order_cnt as '有效订单量',
    t.aov as '客单价',
    c.unique_customer_cnt as '去重买家数',
    f.fail_rate_pct as '终态失败率(%)',
    d.avg_delivery_days as '平均配送天数(天)'
from trade_kpi t
         cross join customer_kpi c
         cross join fail_kpi f
         cross join delivery_kpi d;

with payment_agg as (
    select
        order_id,
        sum(payment_value) as payment_value
    from olist_order_payments_dataset
    group by order_id
),

     state_trade as (
         select
             c.customer_state,
             round(sum(p.payment_value), 2) as gmv,
             count(distinct o.order_id) as valid_order_cnt,
             round(sum(p.payment_value) / count(distinct o.order_id), 2) as aov
         from olist_orders_dataset o
                  join payment_agg p
                       on o.order_id = p.order_id
                  join olist_customers_dataset c
                       on o.customer_id = c.customer_id
         where o.order_status = 'delivered'
         group by c.customer_state
     ),

     state_fail as (
         select
             c.customer_state,
             round(
                     sum(o.order_status in ('canceled', 'unavailable')) / count(*) * 100,
                     2
             ) as fail_rate_pct
         from olist_orders_dataset o
                  join olist_customers_dataset c
                       on o.customer_id = c.customer_id
         where o.order_status in ('delivered', 'canceled', 'unavailable')
         group by c.customer_state
     ),

     state_delivery as (
         select
             c.customer_state,
             round(avg(datediff(o.order_delivered_customer_date, o.order_purchase_timestamp)), 2) as avg_delivery_days
         from olist_orders_dataset o
                  join olist_customers_dataset c
                       on o.customer_id = c.customer_id
         where o.order_status = 'delivered'
           and o.order_delivered_customer_date is not null
           and o.order_purchase_timestamp is not null
         group by c.customer_state
     )

select
    t.customer_state as '客户州',
    t.gmv as '成交总额',
    t.valid_order_cnt as '有效订单量',
    t.aov as '客单价',
    f.fail_rate_pct as '终态失败率(%)',
    d.avg_delivery_days as '平均配送天数(天)'
from state_trade t
         join state_fail f
              on t.customer_state = f.customer_state
         join state_delivery d
              on t.customer_state = d.customer_state
order by t.gmv desc;

with credit_card_order as (
    select
        p.order_id,
        max(p.payment_installments) as payment_installments,
        sum(p.payment_value) as order_payment_value
    from olist_order_payments_dataset p
             join olist_orders_dataset o
                  on p.order_id = o.order_id
    where p.payment_type = 'credit_card'
      and o.order_status = 'delivered'
    group by p.order_id
),
     installment_base as (
         select
             case
                 when payment_installments = 1 then '1. 单期'
                 when payment_installments between 2 and 5 then '2. 低分期（2-5期）'
                 when payment_installments between 6 and 10 then '3. 中分期（6-10期）'
                 when payment_installments > 10 then '4. 高分期（10期以上）'
                 else '未知'
                 end as installment_group,
             order_payment_value
         from credit_card_order
     )
select
    installment_group as '分期档位',
    count(*) as '订单数',
    round(avg(order_payment_value), 2) as '平均订单金额'
from installment_base
 group by installment_group
order by installment_group;