Free FOCUS analysis exercise: dataset, SQL and findings memo

-- Q1. Sanity check: BilledCost vs EffectiveCost by ChargeCategory for the quarter
-- Why they differ:
--   * Purchase: BilledCost is the cash invoiced for commitments (the AWS
--     all-upfront Savings Plan and the monthly Azure reservation payments).
--     EffectiveCost is 0 on these rows because FOCUS spreads that cost onto
--     the usage the commitment covers.
--   * Usage: covered usage has BilledCost 0 and EffectiveCost = the amortized
--     commitment amount, and Unused commitment rows add EffectiveCost with no
--     BilledCost, so Usage EffectiveCost is higher than Usage BilledCost.
--     Google rows go the other way in this dataset: BilledCost is gross and
--     EffectiveCost nets the negative x_Credits.
--   * Tax, Credit, Adjustment: EffectiveCost equals BilledCost.
--   * Over the quarter BilledCost is higher overall because the AWS upfront
--     payment covers 365 days but only 92 days have been recognized.
-- @result Q1
SELECT ChargeCategory, row_count, billed_cost, effective_cost, billed_minus_effective
FROM (
    SELECT
        0                                       AS sort_key,
        ChargeCategory,
        count(*)                                AS row_count,
        sum(BilledCost)                         AS billed_cost,
        sum(EffectiveCost)                      AS effective_cost,
        sum(BilledCost) - sum(EffectiveCost)    AS billed_minus_effective
    FROM focus
    GROUP BY ChargeCategory
    UNION ALL
    SELECT 1, 'TOTAL', count(*), sum(BilledCost), sum(EffectiveCost), sum(BilledCost) - sum(EffectiveCost)
    FROM focus
)
ORDER BY sort_key, ChargeCategory;

-- Q2. Commitment reconciliation for the AWS Savings Plan
-- Q2a: by month, BilledCost and EffectiveCost split by CommitmentDiscountStatus,
-- with the Purchase row on its own line (its status is NULL by design).
-- @result Q2a
SELECT
    strftime(ChargeDate, '%Y-%m')                     AS month,
    ChargeCategory,
    coalesce(CommitmentDiscountStatus, '(none)')      AS commitment_status,
    count(*)                                          AS row_count,
    sum(BilledCost)                                   AS billed_cost,
    sum(EffectiveCost)                                AS effective_cost
FROM focus
WHERE ProviderName = 'AWS'
  AND CommitmentDiscountId LIKE 'arn:aws:savingsplans::%'
GROUP BY month, ChargeCategory, commitment_status
ORDER BY month, ChargeCategory = 'Purchase' DESC, commitment_status;

-- Q2b: proof that the EffectiveCost recognized against the commitment is the
-- same every day (upfront / 365). Expect min = max = 410.00 over 92 days.
-- @result Q2b
WITH purchase AS (
    SELECT sum(BilledCost) AS upfront
    FROM focus
    WHERE ChargeCategory = 'Purchase' AND CommitmentDiscountId LIKE 'arn:aws:savingsplans::%'
),
daily AS (
    SELECT ChargeDate, sum(EffectiveCost) AS recognized
    FROM focus
    WHERE ChargeCategory = 'Usage' AND CommitmentDiscountId LIKE 'arn:aws:savingsplans::%'
    GROUP BY ChargeDate
)
SELECT
    (SELECT upfront FROM purchase)                                   AS upfront_billed,
    CAST((SELECT upfront FROM purchase) / 365 AS DECIMAL(18, 2))     AS expected_daily,
    count(*)                                                         AS days,
    min(recognized)                                                  AS min_daily_recognized,
    max(recognized)                                                  AS max_daily_recognized,
    sum(recognized)                                                  AS recognized_in_quarter,
    (SELECT upfront FROM purchase) - sum(recognized)                 AS not_yet_recognized
FROM daily;

-- Q2c: Used vs Unused before and after the fleet was downsized on 2026-08-15.
-- @result Q2c
SELECT
    CASE WHEN ChargeDate < DATE '2026-08-15' THEN '1: 2026-07-01 to 2026-08-14'
         ELSE '2: 2026-08-15 to 2026-09-30' END                       AS period,
    count(DISTINCT ChargeDate)                                        AS days,
    sum(EffectiveCost)                                                AS recognized,
    sum(EffectiveCost) FILTER (WHERE CommitmentDiscountStatus = 'Used')   AS used,
    coalesce(sum(EffectiveCost) FILTER (WHERE CommitmentDiscountStatus = 'Unused'), 0) AS unused,
    round(100.0 * coalesce(sum(EffectiveCost) FILTER (WHERE CommitmentDiscountStatus = 'Unused'), 0)
          / sum(EffectiveCost), 1)                                    AS unused_pct,
    CAST(coalesce(sum(EffectiveCost) FILTER (WHERE CommitmentDiscountStatus = 'Unused'), 0)
         / count(DISTINCT ChargeDate) AS DECIMAL(18, 2))              AS unused_per_day
FROM focus
WHERE ChargeCategory = 'Usage' AND CommitmentDiscountId LIKE 'arn:aws:savingsplans::%'
GROUP BY ALL
ORDER BY period;

-- Q3. Usage spend by provider and ServiceCategory (EffectiveCost)
-- Different totals do NOT show that one provider is cheaper. Northwind runs
-- different workloads on each provider (web and checkout on AWS, reporting on
-- Azure, ML on Google), so these numbers describe where the money goes, not
-- relative prices. A price comparison needs the same workload on each side.
-- @result Q3
SELECT
    ProviderName,
    ServiceCategory,
    sum(EffectiveCost)                                                         AS effective_cost,
    round(100.0 * sum(EffectiveCost)
          / sum(sum(EffectiveCost)) OVER (PARTITION BY ProviderName), 1)   AS pct_of_provider_usage
FROM focus
WHERE ChargeCategory = 'Usage'
GROUP BY ProviderName, ServiceCategory
ORDER BY ProviderName, effective_cost DESC;

-- Q4. Anomaly detection: daily Usage EffectiveCost per SubAccountId
-- Baseline = the previous 7 days (the current day is excluded). A day is
-- flagged when z = (cost - baseline_mean) / baseline_stddev >= 3. Only Usage
-- rows are used, so month-start Tax, Purchase, Credit and Adjustment rows do
-- not create false alarms. Days without 7 prior days are not scored.
-- Note: once a spike enters the baseline it inflates the mean and stddev, so
-- a flat multi-day spike is usually flagged only on its first day.
-- @result Q4a
WITH calendar AS (
    SELECT DISTINCT ChargeDate FROM focus WHERE ChargeCategory = 'Usage'
),
subs AS (
    SELECT DISTINCT ProviderName, SubAccountId FROM focus WHERE ChargeCategory = 'Usage'
),
daily AS (
    SELECT s.ProviderName, s.SubAccountId, c.ChargeDate,
           coalesce(sum(f.EffectiveCost), 0) AS cost
    FROM subs s
    CROSS JOIN calendar c
    LEFT JOIN focus f
      ON f.ChargeCategory = 'Usage'
     AND f.SubAccountId = s.SubAccountId
     AND f.ChargeDate = c.ChargeDate
    GROUP BY ALL
),
scored AS (
    SELECT *,
        avg(cost)         OVER w AS baseline_mean,
        stddev_samp(cost) OVER w AS baseline_sd,
        count(*)          OVER w AS baseline_days
    FROM daily
    WINDOW w AS (PARTITION BY SubAccountId ORDER BY ChargeDate
                 ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING)
)
SELECT
    ProviderName,
    SubAccountId,
    ChargeDate,
    cost                                                       AS effective_cost,
    CAST(baseline_mean AS DECIMAL(18, 2))                      AS baseline_mean,
    round((cost - baseline_mean) / nullif(baseline_sd, 0), 1)  AS z_score
FROM scored
WHERE baseline_days = 7
  AND (cost - baseline_mean) / nullif(baseline_sd, 0) >= 3
ORDER BY ChargeDate, SubAccountId;

-- Q4b: drill into the flagged sub-account and days by the Tags "team" key,
-- comparing each team's cost on the day with its own previous-7-day average.
-- @result Q4b
WITH calendar AS (
    SELECT DISTINCT ChargeDate FROM focus WHERE ChargeCategory = 'Usage'
),
subs AS (
    SELECT DISTINCT SubAccountId FROM focus WHERE ChargeCategory = 'Usage'
),
daily AS (
    SELECT s.SubAccountId, c.ChargeDate, coalesce(sum(f.EffectiveCost), 0) AS cost
    FROM subs s CROSS JOIN calendar c
    LEFT JOIN focus f
      ON f.ChargeCategory = 'Usage' AND f.SubAccountId = s.SubAccountId AND f.ChargeDate = c.ChargeDate
    GROUP BY ALL
),
flagged AS (
    SELECT SubAccountId, ChargeDate
    FROM (
        SELECT *,
            avg(cost) OVER w AS m, stddev_samp(cost) OVER w AS sd, count(*) OVER w AS n
        FROM daily
        WINDOW w AS (PARTITION BY SubAccountId ORDER BY ChargeDate ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING)
    )
    WHERE n = 7 AND (cost - m) / nullif(sd, 0) >= 3
),
team_daily AS (
    SELECT SubAccountId, ChargeDate, team,
           coalesce(string_agg(DISTINCT json_extract_string(Tags, '$.job'), ','), '(none)') AS job_tag,
           sum(EffectiveCost) AS cost
    FROM focus
    WHERE ChargeCategory = 'Usage'
    GROUP BY ALL
)
SELECT
    t.SubAccountId,
    t.ChargeDate,
    t.team,
    t.job_tag,
    t.cost                                    AS effective_cost,
    CAST(avg(b.cost) AS DECIMAL(18, 2))          AS team_prev_7d_avg,
    t.cost - CAST(avg(b.cost) AS DECIMAL(18, 2)) AS increase
FROM team_daily t
JOIN flagged f USING (SubAccountId, ChargeDate)
LEFT JOIN team_daily b
  ON b.SubAccountId = t.SubAccountId AND b.team = t.team
 AND b.ChargeDate BETWEEN t.ChargeDate - 7 AND t.ChargeDate - 1
GROUP BY t.SubAccountId, t.ChargeDate, t.team, t.job_tag, t.cost
ORDER BY t.ChargeDate, increase DESC;

-- Q5. Unit economics: cost per 1,000 orders by month
-- All charge categories are included (Usage, Purchase, Tax, Credit,
-- Adjustment). The EffectiveCost version spreads commitments over the period
-- they cover. The BilledCost version shows the cash view: July carries the
-- whole AWS upfront payment, so July looks far more expensive per order than
-- it was, and August and September look cheaper.
-- The last column uses Usage rows only. It removes one-off items that FOCUS
-- does not amortize: Tax (EffectiveCost = BilledCost, so July's tax on the
-- upfront payment lands in July), the August Credit and the September
-- Adjustment. Use it to read the operational trend.
-- @result Q5
WITH cost AS (
    SELECT strftime(ChargeDate, '%Y-%m') AS month,
           sum(EffectiveCost) AS effective_cost,
           sum(BilledCost)    AS billed_cost,
           sum(EffectiveCost) FILTER (WHERE ChargeCategory = 'Usage') AS usage_effective_cost
    FROM focus
    GROUP BY ALL
),
ord AS (
    SELECT strftime(order_date, '%Y-%m') AS month, CAST(sum(orders) AS BIGINT) AS orders
    FROM orders
    GROUP BY ALL
)
SELECT
    month,
    orders,
    effective_cost,
    billed_cost,
    CAST(effective_cost * 1000 / orders AS DECIMAL(18, 2)) AS effective_cost_per_1k_orders,
    CAST(billed_cost * 1000 / orders AS DECIMAL(18, 2))    AS billed_cost_per_1k_orders,
    CAST(usage_effective_cost * 1000 / orders AS DECIMAL(18, 2)) AS usage_effective_cost_per_1k_orders
FROM cost JOIN ord USING (month)
ORDER BY month;

-- Reconciliation assertions. Every row must show passed = true.
-- Each check also requires that the rows it tests exist, so a typo in a filter
-- cannot make a check pass on zero rows.