题解 | 最畅销的SKU

with
    temp0 as (
        select
            max(sale_date) as latest_date
        from
            sales_daily_
    ),
    temp1 as (
        select
            sd.store_id,
            sd.sku_id,
            ifnull(sum(qty), 0) as last7d_qty
        from
            sales_daily_ sd
            inner join temp0 t0
        where
            timestampdiff(day, sd.sale_date, t0.latest_date) <= 7
        group by
            sd.store_id,
            sd.sku_id
    )
select
    store_id,
    store_name,
    city,
    sku_id,
    last7d_qty,
    avg_daily_qty,
    stock_qty,
    coverage_days,
    rank_in_store
from
    (
        select
            ss.store_id,
            si.store_name,
            si.city,
            ss.sku_id,
            ifnull(last7d_qty, 0) as last7d_qty,
            ifnull(round(last7d_qty / 7, 2), 0) as avg_daily_qty,
            stock_qty,
            CASE
                WHEN round(last7d_qty / 7, 2) > 0 THEN ROUND(stock_qty / round(last7d_qty / 7, 2), 1)
                ELSE NULL
            END as coverage_days,
            row_number() over (
                partition by
                    si.store_id
                order by
                    last7d_qty DESC,
                    ss.sku_id ASC
            ) as rank_in_store
        from
            store_stock_ ss
            left join store_info_ si on ss.store_id = si.store_id
            left join temp1 t1 on ss.store_id = t1.store_id
            and ss.sku_id = t1.sku_id
    ) as t
where
    t.rank_in_store <= 3