Read the exact SQL
This is the aggregate query recorded in the evidence file. The examples query is recorded beside it.
WITH live AS (
SELECT model, kind, status, created_at, cost_estimate_micro, provider_charged_micro,
CASE
WHEN status = 'saved' AND completed_at IS NOT NULL THEN completed_at
WHEN status = 'cancelled' AND cancelled_at IS NOT NULL THEN cancelled_at
ELSE NULL
END AS terminal_at
FROM generations
WHERE deleted_at IS NULL
), saved AS (
SELECT model, kind, terminal_at - created_at AS elapsed_ms,
ROW_NUMBER() OVER (PARTITION BY model, kind ORDER BY terminal_at - created_at) AS rn,
COUNT(*) OVER (PARTITION BY model, kind) AS n
FROM live
WHERE status = 'saved' AND terminal_at IS NOT NULL
), costs AS (
SELECT model, kind, cost_estimate_micro,
ROW_NUMBER() OVER (PARTITION BY model, kind ORDER BY cost_estimate_micro) AS rn,
COUNT(*) OVER (PARTITION BY model, kind) AS n
FROM live
)
SELECT l.model, l.kind,
COUNT(*) AS jobs,
SUM(l.status = 'saved') AS saved,
SUM(l.status = 'failed') AS failed,
SUM(l.status = 'unknown') AS unknown,
SUM(l.status = 'cancelled') AS cancelled,
MIN(l.created_at) AS first_created_ms,
MAX(l.created_at) AS last_created_ms,
SUM(l.terminal_at IS NULL) AS without_terminal,
(SELECT (MAX(CASE WHEN s.rn = (s.n + 1) / 2 THEN s.elapsed_ms END) + MAX(CASE WHEN s.rn = (s.n + 2) / 2 THEN s.elapsed_ms END)) / 2.0
FROM saved s WHERE s.model = l.model AND s.kind = l.kind) AS saved_median_ms,
(SELECT MAX(CASE WHEN s.rn = (9 * s.n + 9) / 10 THEN s.elapsed_ms END)
FROM saved s WHERE s.model = l.model AND s.kind = l.kind) AS saved_p90_ms,
SUM(l.cost_estimate_micro) AS cost_estimate_sum_micro,
(SELECT (MAX(CASE WHEN c.rn = (c.n + 1) / 2 THEN c.cost_estimate_micro END) + MAX(CASE WHEN c.rn = (c.n + 2) / 2 THEN c.cost_estimate_micro END)) / 2.0
FROM costs c WHERE c.model = l.model AND c.kind = l.kind) AS cost_median_micro,
SUM(l.provider_charged_micro IS NOT NULL) AS provider_charge_rows
FROM live l
GROUP BY l.model, l.kind
ORDER BY jobs DESC, l.modelExamples query:
WITH picked AS (
SELECT kind, model,
json_extract(request_json, '$.aspect') AS aspect,
json_extract(request_json, '$.durationSeconds') AS duration_seconds,
json_extract(request_json, '$.aspectRatio') AS aspect_ratio,
json_extract(request_json, '$.outputWidth') AS output_width,
json_extract(request_json, '$.outputHeight') AS output_height,
ROW_NUMBER() OVER (PARTITION BY kind ORDER BY created_at DESC) AS rn
FROM generations
WHERE deleted_at IS NULL AND status = 'saved'
)
SELECT kind, model, aspect, duration_seconds, aspect_ratio, output_width, output_height
FROM picked
WHERE rn = 1
ORDER BY kind