题解 | 最畅销的SKU

WITH t AS (
    SELECT s.store_id,
           store_name,
           city,
           s1.sku_id,
           COALESCE(SUM(qty),0) AS last7d_qty,
           ROUND(COALESCE(SUM(qty),0)/7,2) AS avg_daily_qty,
           MAX(stock_qty) AS stock_qty,
           CASE WHEN ROUND(COALESCE(SUM(qty),0)/7,2)>0 THEN ROUND(MAX(stock_qty)/ROUND(COALESCE(SUM(qty),0)/7,2), 1) ELSE NULL END AS coverage_days

    FROM store_info_ s 
    LEFT JOIN store_stock_ s1 ON s.store_id=s1.store_id
    LEFT JOIN sales_daily_ s2 ON s1.store_id=s2.store_id AND s1.sku_id=s2.sku_id AND DATEDIFF((SELECT MAX(snapshot_date)FROM store_stock_),sale_date) BETWEEN 0 AND 6
    GROUP BY s.store_id,
             store_name,
             city,
             s1.sku_id
),
t1 AS 
(
    SELECT *,
           ROW_NUMBER() OVER(PARTITION BY store_id ORDER BY  last7d_qty DESC,sku_id ASC ) AS rank_in_store
    FROM t
)
SELECT *
FROM t1 
WHERE rank_in_store<=3