Coding Agents for Enterprise

question: "Why did margins drop in the Northeast last quarter?"

decomposition:
  1. Calculate current quarter margin for Northeast
  2. Calculate previous quarter margin for comparison
  3. Identify margin components (revenue, COGS, discounts)
  4. Compare each component period-over-period
  5. Identify largest contributors to change
  6. Generate root cause explanation

-- Step 1: Calculate margin components by quarter
WITH quarterly_metrics AS (
    SELECT
        fiscal_quarter,
        region,
        SUM(revenue) as total_revenue,
        SUM(cogs) as total_cogs,
        SUM(discounts) as total_discounts,
        SUM(shipping) as total_shipping
    FROM sales_fact sf
    JOIN dim_branch b ON sf.branch_id = b.id
    WHERE b.region = 'Northeast'
      AND sf.transaction_type NOT IN ('internal_transfer')  -- Tribal knowledge
    GROUP BY fiscal_quarter, region
)
SELECT
    fiscal_quarter,
    total_revenue,
    total_cogs,
    total_discounts,
    (total_revenue - total_cogs - total_discounts - total_shipping)
        / NULLIF(total_revenue, 0) as margin_pct
FROM quarterly_metrics
ORDER BY fiscal_quarter DESC;

SELECT * FROM inventory;
-- Returns raw data, user must interpret

Analysis of inventory health across all locations:

OVERSTOCK (>90 days supply):
- 847 SKUs, 2.3M tied up
- Highest: Widget A (450K, 180 days)

DEADSTOCK (No movement 12 months):
- 234 SKUs, 890K at risk
- Mostly discontinued items from 2024

LOW STOCK (<7 days supply):
- 56 SKUs, potential stockout risk
- 12 are fast-movers, recommend reorder

RECOMMENDED ACTIONS:
1. Transfer overstock Widget A to high-demand branches
2. Markdown deadstock items 40%+
3. Expedite PO #4521 for low-stock items