lesson 57 Advanced product analysis · advanced

AI usage cost & contribution margin

Compare usage revenue with costs to calculate contribution margin.

the idea

Usage cost equals input tokens × input rate plus output tokens × output rate, divided by one million. These rates are fictional. Under this exercise’s rule, successful requests are chargeable; errors are excluded. Compare January billed revenue to January successful usage cost, not all-time billing.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

SELECT
  r.model,
  ROUND(SUM(r.input_tokens*m.input_per_million+ r.output_tokens*m.output_per_million)/1000000.0,6) AS usage_cost
FROM ai_requests r
JOIN model_rates m ON m.model=r.model
WHERE r.status='success'
GROUP BY r.model;

Watch for: This simplified contribution margin excludes other costs and refunds; it is not net profit.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Price a request. These teaching rates are fictional, in USD per million tokens.

   step-1.sql Run

   sql›

   SELECT
     r.id,
     r.model,
     (r.input_tokens*m.input_per_million+r.output_tokens*m.output_per_million)/1000000.0 AS usage_cost
   FROM ai_requests r
   JOIN model_rates m ON m.model=r.model
   WHERE r.status='success';

2. 2

   Compare aligned January totals. Billed revenue and usage costs share the same account and period grain.

   step-2.sql Run

   sql›

   WITH costs AS(
     SELECT
       r.account_id,
       SUM(r.input_tokens*m.input_per_million+r.output_tokens*m.output_per_million)/1000000.0 AS usage_cost
     FROM ai_requests r
     JOIN model_rates m ON m.model=r.model
     WHERE r.status='success'
       AND r.requested_at>='2025-01-01'
       AND r.requested_at<'2025-02-01'
     GROUP BY r.account_id
   ),billing AS(
     SELECT account_id,SUM(billed_amount) AS billed_amount
     FROM invoices
     WHERE period='2025-01'
     GROUP BY account_id
   )
   SELECT
     a.id,
     COALESCE(b.billed_amount,0) AS billed_amount,
     ROUND(COALESCE(c.usage_cost,0),6) AS usage_cost,
     ROUND(COALESCE(b.billed_amount,0)-COALESCE(c.usage_cost,0),6) AS contribution_margin
   FROM ai_accounts a
   LEFT JOIN costs c ON c.account_id=a.id
   LEFT JOIN billing b ON b.account_id=a.id