Spaces:
Running on Zero
Running on Zero
| -- Shrinkage analysis | |
| -- Shrink % = value lost through stock adjustments / sales revenue. | |
| -- name: shrink_by_branch | |
| WITH s AS ( | |
| SELECT branch_id, SUM(revenue) AS revenue FROM sales GROUP BY branch_id | |
| ), | |
| a AS ( | |
| SELECT branch_id, SUM(cost_value) AS shrink_value FROM stock_adjustments GROUP BY branch_id | |
| ) | |
| SELECT b.branch_id, | |
| b.branch_name, | |
| ROUND(s.revenue, 2) AS revenue, | |
| ROUND(a.shrink_value, 2) AS shrink_value, | |
| ROUND(100.0 * a.shrink_value / s.revenue, 2) AS shrink_pct | |
| FROM branches b | |
| JOIN s ON s.branch_id = b.branch_id | |
| JOIN a ON a.branch_id = b.branch_id | |
| ORDER BY shrink_pct DESC; | |
| -- name: shrink_by_category_reason | |
| SELECT p.category, | |
| a.reason, | |
| SUM(a.qty) AS units_lost, | |
| ROUND(SUM(a.cost_value), 2) AS shrink_value | |
| FROM stock_adjustments a | |
| JOIN products p ON p.product_id = a.product_id | |
| GROUP BY p.category, a.reason | |
| ORDER BY p.category, shrink_value DESC; | |
| -- name: shrink_monthly_trend | |
| WITH s AS ( | |
| SELECT substr(date, 1, 7) AS month, SUM(revenue) AS revenue FROM sales GROUP BY month | |
| ), | |
| a AS ( | |
| SELECT substr(date, 1, 7) AS month, SUM(cost_value) AS shrink_value | |
| FROM stock_adjustments GROUP BY month | |
| ) | |
| SELECT s.month, | |
| ROUND(s.revenue, 2) AS revenue, | |
| ROUND(a.shrink_value, 2) AS shrink_value, | |
| ROUND(100.0 * a.shrink_value / s.revenue, 2) AS shrink_pct | |
| FROM s JOIN a ON a.month = s.month | |
| ORDER BY s.month; | |
| -- name: top_shrink_products | |
| SELECT p.product_id, | |
| p.product_name, | |
| p.category, | |
| SUM(a.qty) AS units_lost, | |
| ROUND(SUM(a.cost_value), 2) AS shrink_value | |
| FROM stock_adjustments a | |
| JOIN products p ON p.product_id = a.product_id | |
| GROUP BY p.product_id | |
| ORDER BY shrink_value DESC | |
| LIMIT 10; | |