Cloud unit economics from FOCUS data: cost per 1,000 orders in SQL

CREATE OR REPLACE VIEW focus AS
SELECT *,
       json_extract_string(Tags, '$.team') AS team,
       json_extract_string(Tags, '$.env')  AS env,
       CAST(ChargePeriodStart AS DATE)     AS day
FROM read_csv_auto('focus_exercise_2026-q3.csv');

CREATE OR REPLACE VIEW orders AS
SELECT CAST(date AS DATE) AS day, orders
FROM read_csv_auto('northwind_orders_2026-q3.csv');

-- Which teams serve orders? This mapping is a business decision, not something FOCUS knows.
-- Customer-facing: the services an order actually passes through.
-- Shared: platform costs every team uses (including the Savings Plan's Unused rows).
-- Internal: analytics, data engineering and ML work that does not scale with orders.
CREATE OR REPLACE VIEW cost_classes AS
SELECT *,
       CASE
         WHEN team IN ('checkout', 'search', 'web') THEN 'customer_facing'
         WHEN team = 'platform'                     THEN 'shared'
         WHEN team IS NULL                          THEN 'untagged'
         ELSE 'internal'
       END AS cost_class
FROM focus;

-- U1. The numerator decides the answer. Same quarter, same orders, four defensible numbers.
--     All on EffectiveCost (amortized), never BilledCost.
WITH monthly_cost AS (
  SELECT date_trunc('month', day) AS month,
         SUM(EffectiveCost)                                                            AS everything,
         SUM(EffectiveCost) FILTER (WHERE ChargeCategory = 'Usage')                    AS usage_only,
         SUM(EffectiveCost) FILTER (WHERE ChargeCategory = 'Usage' AND env = 'prod')   AS prod_usage,
         SUM(EffectiveCost) FILTER (WHERE cost_class = 'customer_facing' AND env = 'prod') AS customer_facing_prod
  FROM cost_classes
  GROUP BY 1
),
monthly_orders AS (
  SELECT date_trunc('month', day) AS month, SUM(orders) AS orders FROM orders GROUP BY 1
)
SELECT strftime(c.month, '%Y-%m')                               AS month,
       o.orders,
       ROUND(c.everything           / o.orders * 1000, 2) AS all_cost_per_1k,
       ROUND(c.usage_only           / o.orders * 1000, 2) AS usage_per_1k,
       ROUND(c.prod_usage           / o.orders * 1000, 2) AS prod_usage_per_1k,
       ROUND(c.customer_facing_prod / o.orders * 1000, 2) AS customer_facing_per_1k
FROM monthly_cost c JOIN monthly_orders o USING (month)
ORDER BY 1;

-- U2. Allocate shared platform cost instead of dropping it. Platform spend is split across
--     teams in proportion to each team's own prod usage that month (one common rule; others
--     are defensible). The customer-facing share is then added to the numerator.
WITH team_month AS (
  SELECT date_trunc('month', day) AS month, cost_class, SUM(EffectiveCost) AS cost
  FROM cost_classes
  WHERE ChargeCategory = 'Usage' AND (env = 'prod' OR cost_class = 'shared')
  GROUP BY 1, 2
),
pivoted AS (
  SELECT month,
         SUM(cost) FILTER (WHERE cost_class = 'customer_facing') AS customer_facing,
         SUM(cost) FILTER (WHERE cost_class = 'internal')        AS internal,
         SUM(cost) FILTER (WHERE cost_class = 'shared')          AS shared
  FROM team_month GROUP BY 1
),
monthly_orders AS (
  SELECT date_trunc('month', day) AS month, SUM(orders) AS orders FROM orders GROUP BY 1
)
SELECT strftime(p.month, '%Y-%m') AS month,
       ROUND(p.customer_facing, 2) AS customer_facing,
       ROUND(p.shared, 2) AS shared_platform,
       ROUND(p.customer_facing / (p.customer_facing + p.internal), 3) AS customer_facing_share,
       ROUND(p.customer_facing / o.orders * 1000, 2) AS direct_per_1k,
       ROUND((p.customer_facing + p.shared * p.customer_facing / (p.customer_facing + p.internal))
             / o.orders * 1000, 2) AS with_platform_share_per_1k
FROM pivoted p JOIN monthly_orders o USING (month)
ORDER BY 1;

-- U2b. Where did the "savings" go? After the checkout fleet was downsized on 2026-08-15, part
--      of its Savings Plan went unused. FOCUS records that as Usage rows with
--      CommitmentDiscountStatus = 'Unused', tagged to the platform team that owns the plan,
--      so checkout looks cheaper while the idle commitment lands in "shared". Charging the
--      Unused cost back to the workload the commitment was bought for gives the honest number.
WITH monthly AS (
  SELECT date_trunc('month', day) AS month,
         SUM(EffectiveCost) FILTER (WHERE cost_class = 'customer_facing' AND env = 'prod') AS customer_facing,
         SUM(EffectiveCost) FILTER (WHERE CommitmentDiscountStatus = 'Unused')            AS unused_commitment
  FROM cost_classes
  WHERE ChargeCategory = 'Usage'
  GROUP BY 1
),
monthly_orders AS (
  SELECT date_trunc('month', day) AS month, SUM(orders) AS orders FROM orders GROUP BY 1
)
SELECT strftime(m.month, '%Y-%m') AS month,
       ROUND(COALESCE(m.unused_commitment, 0), 2) AS unused_commitment,
       ROUND(m.customer_facing / o.orders * 1000, 2) AS direct_per_1k,
       ROUND((m.customer_facing + COALESCE(m.unused_commitment, 0)) / o.orders * 1000, 2) AS with_unused_charged_back_per_1k
FROM monthly m JOIN monthly_orders o USING (month)
ORDER BY 1;

-- U3. Grain: daily ratios are noisy. Average the RATIO and you weight a quiet Sunday the same
--     as a peak day; divide the SUMS over a rolling window instead.
WITH daily AS (
  SELECT c.day,
         SUM(c.EffectiveCost) FILTER (WHERE c.cost_class = 'customer_facing' AND c.env = 'prod') AS cost,
         MAX(o.orders) AS orders
  FROM cost_classes c JOIN orders o USING (day)
  GROUP BY 1
),
windowed AS (
  SELECT day,
         cost / orders * 1000 AS daily_per_1k,
         AVG(cost / orders * 1000) OVER w AS avg_of_ratios_7d,
         SUM(cost) OVER w / SUM(orders) OVER w * 1000 AS ratio_of_sums_7d
  FROM daily
  WINDOW w AS (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
)
SELECT strftime(day, '%Y-%m-%d') AS day,
       ROUND(daily_per_1k, 2) AS daily_per_1k,
       ROUND(avg_of_ratios_7d, 2) AS avg_of_ratios_7d,
       ROUND(ratio_of_sums_7d, 2) AS ratio_of_sums_7d
FROM windowed
WHERE day IN (DATE '2026-07-07', DATE '2026-08-05', DATE '2026-09-02', DATE '2026-09-30')
ORDER BY 1;

-- U4. Is cost actually driven by orders? Index weekly orders and weekly customer-facing cost to
--     the first full week (= 100). If orders grow and cost does not, the falling cost per
--     order is a volume effect on mostly fixed capacity, not an efficiency gain, and it will
--     reverse if volume drops. (A cost-on-orders regression is tempting here, but both series
--     trend over time and the fleet changed size mid-quarter, so its slope is meaningless.)
WITH daily AS (
  SELECT c.day,
         SUM(c.EffectiveCost) FILTER (WHERE c.cost_class = 'customer_facing' AND c.env = 'prod') AS cost,
         MAX(o.orders) AS orders
  FROM cost_classes c JOIN orders o USING (day)
  GROUP BY 1
),
weekly AS (
  SELECT date_trunc('week', day) AS week, SUM(cost) AS cost, SUM(orders) AS orders, COUNT(*) AS days
  FROM daily GROUP BY 1
  HAVING COUNT(*) = 7
)
SELECT strftime(week, '%Y-%m-%d') AS week_starting,
       orders,
       ROUND(cost, 2) AS customer_facing_cost,
       ROUND(100.0 * orders / FIRST_VALUE(orders) OVER (ORDER BY week), 1) AS orders_index,
       ROUND(100.0 * cost / FIRST_VALUE(cost) OVER (ORDER BY week), 1)     AS cost_index,
       ROUND(cost / orders * 1000, 2) AS cost_per_1k
FROM weekly
ORDER BY week;

-- U5. Why not BilledCost? The same customer-facing numerator, billed instead of effective.
--     Covered usage bills at 0 because the Savings Plan was paid for upfront in July.
WITH monthly AS (
  SELECT date_trunc('month', c.day) AS month,
         SUM(c.EffectiveCost) FILTER (WHERE c.cost_class = 'customer_facing' AND c.env = 'prod') AS effective,
         SUM(c.BilledCost)    FILTER (WHERE c.cost_class = 'customer_facing' AND c.env = 'prod') AS billed
  FROM cost_classes c GROUP BY 1
),
monthly_orders AS (
  SELECT date_trunc('month', day) AS month, SUM(orders) AS orders FROM orders GROUP BY 1
)
SELECT strftime(m.month, '%Y-%m') AS month,
       ROUND(m.effective / o.orders * 1000, 2) AS effective_per_1k,
       ROUND(m.billed    / o.orders * 1000, 2) AS billed_per_1k
FROM monthly m JOIN monthly_orders o USING (month)
ORDER BY 1;