Write a SQL query to analyze LEGO Group store performance by joining sales, inventory, and staffing tables to identify locations with low stock and declining revenue. Use the latest sales month for the comparison. Treat a location as low stock when total stock is below its total reorder level, and include the latest available staffing data for each location.
store_id, current_revenue, prior_revenue, revenue_change_pct, stock_units, reorder_level, employee_count, and scheduled_hours.revenue_change_pct ascending, then store_id.| Column | Type | Description |
|---|---|---|
| sale_idPK | INTEGER | Unique sales record identifier |
| store_id | VARCHAR(20) | LEGO Group store identifier |
| sale_month | DATE | First day of the sales month |
| revenue | NUMERIC(12,2) | Revenue recorded by the sales row |
| Column | Type | Description |
|---|---|---|
| inventory_idPK | INTEGER | Unique inventory record identifier |
| store_id | VARCHAR(20) | LEGO Group store identifier |
| sku | VARCHAR(30) | LEGO product SKU |
| stock_units | INTEGER | Units currently in stock |
| reorder_level | INTEGER | Minimum desired units before replenishment |
| Column | Type | Description |
|---|---|---|
| staffing_idPK | INTEGER | Unique staffing record identifier |
| store_id | VARCHAR(20) | LEGO Group store identifier |
| staff_month | DATE | Month covered by the staffing record |
| employee_count | INTEGER | Number of employees assigned to the store |
| scheduled_hours | NUMERIC(10,2) | Scheduled staff hours for the month |