Queries Spend

SELECT min(finalized_at) AS first_receipt, count() AS receipts
FROM system.signal_spend
WHERE finalized_at IS NOT NULL

SELECT
    toDate(toTimeZone(finalized_at, 'UTC')) AS day,
    kind,
    sum(credits) AS recorded_credits,
    sumIf(credits, billing_exempt) AS exempt_credits,
    countIf(accounting_failed) AS incomplete_records
FROM system.signal_spend
WHERE finalized_at >= {date_from} AND finalized_at < {date_to}
GROUP BY day, kind
ORDER BY day, kind

SELECT
    nullIf(JSONExtractString(ifNull(m.metadata, '{}'), 'source_product'), '') AS source_product,
    nullIf(JSONExtractString(ifNull(m.metadata, '{}'), 'source_type'), '') AS source_type,
    sum(a.credits) AS recorded_credits,
    sumIf(a.credits, a.billing_exempt) AS exempt_credits,
    sumIf(a.credits, a.kind = 'ad-hoc') AS processing_credits,
    sumIf(a.credits, a.kind IN ('research', 'repo_selection')) AS research_credits,
    sumIf(a.credits, a.kind IN ('reviewer_triggered_implementation', 'scout_triggered_implementation')) AS implementation_credits
FROM (
    SELECT
        s.credits AS credits,
        s.billing_exempt AS billing_exempt,
        s.kind AS kind,
        if(s.report_key != '', r.trigger_key, s.signal_key) AS signal_key
    FROM (
        SELECT
            credits, billing_exempt, kind,
            ifNull(toString(report_id), '') AS report_key,
            ifNull(toString(signal_id), '') AS signal_key
        FROM system.signal_spend
        WHERE finalized_at >= {date_from} AND finalized_at < {date_to}
            AND kind != 'scout_run'
    ) AS s
    LEFT JOIN (
        SELECT toString(id) AS rid, toString(triggering_signal_id) AS trigger_key
        FROM system.inbox_reports
    ) AS r ON s.report_key = r.rid
) AS a
LEFT JOIN (
    SELECT document_id, argMax(metadata, inserted_at) AS metadata
    FROM document_embeddings
    WHERE model_name = 'text-embedding-3-small-1536'
        AND product = 'signals' AND document_type = 'signal' AND rendering = 'plain'
    GROUP BY document_id
) AS m ON a.signal_key = m.document_id
GROUP BY source_product, source_type
ORDER BY recorded_credits DESC

SELECT
    r.skill_name AS scout,
    uniqExact(s.run_key) AS runs,
    sum(s.credits) AS recorded_credits,
    sumIf(s.credits, s.billing_exempt) AS exempt_credits,
    uniqExactIf(s.run_key,
        JSONLength(ifNull(r.emitted_report_ids, '[]')) = 0
        AND JSONLength(ifNull(r.edited_report_ids, '[]')) = 0) AS empty_runs,
    sumIf(s.credits,
        JSONLength(ifNull(r.emitted_report_ids, '[]')) = 0
        AND JSONLength(ifNull(r.edited_report_ids, '[]')) = 0) AS empty_run_credits,
    uniqExactIf(s.run_key,
        JSONLength(ifNull(r.edited_report_ids, '[]')) > 0) AS runs_with_edits
FROM (
    SELECT toString(scout_run_id) AS run_key, credits, billing_exempt
    FROM system.signal_spend
    WHERE kind = 'scout_run'
        AND finalized_at >= {date_from} AND finalized_at < {date_to}
) AS s
LEFT JOIN (
    SELECT toString(id) AS rid, skill_name, emitted_report_ids, edited_report_ids
    FROM system.scout_runs
    WHERE created_at >= {date_from} - INTERVAL 7 DAY
) AS r ON s.run_key = r.rid
GROUP BY scout
ORDER BY recorded_credits DESC