ABC-XYZ Inventory Classification by SQL Aggregations

Business Context

NovaTrade Distributors is a mid-sized B2B wholesale company operating out of three regional warehouses, supplying electronics accessories and peripherals to retail chains across the Midwest. The company manages a catalog of approximately 80 active SKUs and processes over 12,000 sales transactions per year. In a recent quarterly operations review, the supply chain manager flagged a critical inefficiency: the procurement team treats all 80 products with nearly identical reorder cycles and safety stock levels — despite massive differences in revenue contribution and demand variability. This blanket approach has resulted in simultaneous stockouts on high-revenue products and excess dead stock on slow-moving ones, costing the business an estimated $180,000 in lost sales and holding costs over the past fiscal year.

The SQL Challenge

The supply chain manager has requested a product-level ABC-XYZ classification covering the last 12 months of sales data. ABC classification segments products by their total revenue contribution — A items (top revenue tier), B items (mid-tier), and C items (low revenue). XYZ classification segments products by demand variability using the Coefficient of Variation (CV = Standard Deviation / Mean) of their monthly sales quantities — X items are stable (CV < 0.5), Y items are moderately variable (0.5 ≤ CV < 1.0), and Z items are highly unpredictable (CV ≥ 1.0). The query will require aggregations across monthly time buckets, GROUP BY to compute per-product statistics, NTILE() window function to rank products into revenue tiers, and CASE statements to apply the XYZ threshold logic.

Your Role and Deliverable

You are the data analyst at NovaTrade Distributors. The supply chain manager needs a single query output that classifies every active product with both its ABC tier and XYZ tier — combined into a single label (e.g., AX, BZ, CY). The final result should include the product name, category, total revenue, average monthly demand, coefficient of variation, and the combined ABC_XYZ classification — sorted by total revenue descending so the most important products appear first.