Queries Yield

SELECT
    countIf(created_at >= {date_from} AND created_at < {date_to}) AS reports_created,
    countIf(first_visible_at IS NOT NULL AND first_visible_at <= now() - INTERVAL 14 DAY AND first_visible_at >= {yield_from}) AS settled_reports,
    countIf(status = 'resolved' AND first_visible_at IS NOT NULL AND first_visible_at <= now() - INTERVAL 14 DAY AND first_visible_at >= {yield_from}) AS settled_resolved,
    round(settled_resolved / greatest(settled_reports, 1), 3) AS settled_rate
FROM system.inbox_reports
WHERE toString(id) NOT IN (
    SELECT arrayJoin(JSONExtract(ifNull(emitted_report_ids, '[]'), 'Array(String)'))
    FROM system.scout_runs
    WHERE created_at >= {yield_from} - INTERVAL 7 DAY
)

SELECT
    uniqExactIf(e.report_id, e.created_at >= {date_from}) AS reports_created,
    uniqExactIf(e.report_id, r.settled = 1) AS settled_reports,
    uniqExactIf(e.report_id, r.settled = 1 AND r.status = 'resolved') AS settled_resolved,
    round(settled_resolved / greatest(settled_reports, 1), 3) AS settled_rate
FROM (
    SELECT
        created_at,
        arrayJoin(JSONExtract(ifNull(emitted_report_ids, '[]'), 'Array(String)')) AS report_id
    FROM system.scout_runs
    WHERE created_at >= {yield_from}
        AND created_at < {date_to}
) AS e
LEFT JOIN (
    SELECT
        toString(id) AS rid,
        status,
        if(first_visible_at IS NOT NULL AND first_visible_at <= now() - INTERVAL 14 DAY, 1, 0) AS settled
    FROM system.inbox_reports
) AS r ON r.rid = e.report_id

SELECT
    b.source_product AS source_product,
    b.source_type AS source_type,
    uniqExactIf(b.report_id, b.ts >= {date_from}) AS reports_created,
    uniqExactIf(b.report_id, r.settled = 1) AS settled_reports,
    uniqExactIf(b.report_id, r.settled = 1 AND r.status = 'resolved') AS settled_resolved,
    round(settled_resolved / greatest(settled_reports, 1), 3) AS settled_rate
FROM (
    SELECT
        document_id,
        min(timestamp) AS ts,
        argMax(JSONExtractString(metadata, 'source_product'), inserted_at) AS source_product,
        argMax(JSONExtractString(metadata, 'source_type'), inserted_at) AS source_type,
        argMax(JSONExtractString(metadata, 'report_id'), inserted_at) AS report_id
    FROM document_embeddings
    WHERE model_name = 'text-embedding-3-small-1536'
        AND product = 'signals'
        AND document_type = 'signal'
        AND rendering = 'plain'
        AND timestamp >= {yield_from}
        AND timestamp < {date_to}
    GROUP BY document_id
) AS b
LEFT JOIN (
    SELECT
        toString(id) AS rid,
        status,
        if(first_visible_at IS NOT NULL AND first_visible_at <= now() - INTERVAL 14 DAY, 1, 0) AS settled
    FROM system.inbox_reports
) AS r ON r.rid = b.report_id
WHERE b.report_id != ''
GROUP BY source_product, source_type
ORDER BY reports_created DESC

SELECT
    e.scout AS scout,
    uniqExactIf(e.report_id, e.created_at >= {date_from}) AS reports_created,
    uniqExactIf(e.report_id, r.settled = 1) AS settled_reports,
    uniqExactIf(e.report_id, r.settled = 1 AND r.status = 'resolved') AS settled_resolved
FROM (
    SELECT
        skill_name AS scout,
        created_at,
        arrayJoin(JSONExtract(ifNull(emitted_report_ids, '[]'), 'Array(String)')) AS report_id
    FROM system.scout_runs
    WHERE created_at >= {yield_from}
        AND created_at < {date_to}
) AS e
LEFT JOIN (
    SELECT
        toString(id) AS rid,
        status,
        if(first_visible_at IS NOT NULL AND first_visible_at <= now() - INTERVAL 14 DAY, 1, 0) AS settled
    FROM system.inbox_reports
) AS r ON r.rid = e.report_id
GROUP BY scout
ORDER BY reports_created DESC

SELECT e.scout AS scout, r.status AS status, uniqExact(e.report_id) AS reports
FROM (
    SELECT
        skill_name AS scout,
        arrayJoin(JSONExtract(ifNull(emitted_report_ids, '[]'), 'Array(String)')) AS report_id
    FROM system.scout_runs
    WHERE created_at >= {yield_from}
        AND skill_name IN ({scouts})
) AS e
LEFT JOIN (
    SELECT toString(id) AS rid, status
    FROM system.inbox_reports
) AS r ON r.rid = e.report_id
GROUP BY scout, status
ORDER BY scout, reports DESC

SELECT skill_name, created_at, emitted_count, substring(summary, 1, 300) AS summary_start
FROM system.scout_runs
WHERE created_at >= now() - INTERVAL 3 DAY
    AND skill_name IN ({scouts})
    AND summary != ''
ORDER BY created_at DESC
LIMIT 2 BY skill_name