ETL &
Beyond.

-- Model 2: Monthly Recurring Revenue
SELECT
    DATE_TRUNC(payment_date, MONTH) AS month,
    SUM(amount)                    AS monthly_revenue
FROM  payments
GROUP BY month
ORDER BY month;

-- 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;

-- 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;

-- 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;

-- 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;