ETL &
Beyond.

This query ranks every supplier by their average delivery delay, giving the operations team an instant view of which vendors are consistently late and by how much — the backbone of the Supplier Delays dashboard.

-- Vendor performance: avg delay days by supplier
SELECT
    v.vendor_name,
    AVG(DATEDIFF(day, po.expected_date, po.received_date)) AS avg_delay_days,
    COUNT(po.order_id)                                        AS total_orders
FROM  purchase_orders  po
JOIN  vendors          v  ON po.vendor_id = v.vendor_id
WHERE po.received_date IS NOT NULL
GROUP BY v.vendor_name
ORDER BY avg_delay_days DESC;

This query calculates the OSHA-standard incident frequency rate per 200,000 work hours for each plant location — the universal safety benchmark that lets management compare risk levels across sites and flag which facilities need immediate attention.

-- Incident frequency rate per 200,000 work hours
SELECT
    plant_location,
    COUNT(incident_id)                                        AS total_incidents,
    SUM(hours_worked)                                          AS total_hours,
    ROUND(COUNT(incident_id) * 200000.0 / SUM(hours_worked), 2) AS incident_rate
FROM  fact_incidents   fi
JOIN  dim_plants        dp  ON fi.plant_id = dp.plant_id
GROUP BY plant_location
ORDER BY incident_rate DESC;

This is the core fact table load that replaced the 5,000-line monolith — aggregating sales revenue and order counts by date, product, and region into a clean, modular step that all downstream BI reports now depend on.

-- Before: monolithic CASE logic buried in 5,000+ line proc
-- After: isolated, testable transformation step
INSERT INTO fact_sales_summary (date_key, product_key, region_key, total_revenue, order_count)
SELECT
    dk.date_key,
    pk.product_key,
    rk.region_key,
    SUM(s.amount)      AS total_revenue,
    COUNT(s.order_id)  AS order_count
FROM  staging_sales   s
JOIN  dim_date        dk  ON CAST(s.order_date AS DATE) = dk.full_date
JOIN  dim_product     pk  ON s.product_id  = pk.source_product_id
JOIN  dim_region      rk  ON s.region_code = rk.region_code
GROUP BY dk.date_key, pk.product_key, rk.region_key;

This query surfaces the top 20 suppliers by total purchase order value for the current fiscal year, including each supplier's percentage share of overall spend — directly powering the spend concentration view that revealed just 10 suppliers account for 62% of all procurement costs.

-- Supplier spend concentration: top 20 by total PO value
SELECT
    ds.supplier_name,
    ds.category,
    SUM(fpo.line_value)                                               AS total_spend,
    ROUND(SUM(fpo.line_value) * 100.0 / SUM(SUM(fpo.line_value)) OVER(), 2) AS spend_pct
FROM  fact_po_lines   fpo
JOIN  dim_supplier    ds   ON fpo.supplier_key  = ds.supplier_key
JOIN  dim_time        dt   ON fpo.date_key      = dt.date_key
WHERE dt.fiscal_year = YEAR(GETDATE())
GROUP BY ds.supplier_name, ds.category
ORDER BY total_spend DESC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;