SELECT
    id,
    ROUND(final_pass_rate * 100, 1) AS "Final Pass Rate",
    ROUND(EXTRACT(EPOCH FROM (last_ts - start_ts)), 1) AS "Time",
    max_attempts AS "# Tasks",
    ROUND(avg_reasoning_len, 0) AS "Avg Reasoning Chars",
    ROUND(correct_per_1k_chars, 3) AS "Correct / 1k Chars"
FROM (
    SELECT
        id,
        start_ts,
        last_ts,
        MAX(attempt_n) OVER (PARTITION BY id) AS max_attempts,
        passed_so_far * 1.0 / attempt_n AS final_pass_rate,
        avg_reasoning_len,
        (passed_so_far / NULLIF(total_reasoning_len, 0)) * 1000 AS correct_per_1k_chars,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY attempt_n DESC) AS rnk
    FROM (
        SELECT
            id,
            ts,
            attempt_n,
            passed_so_far,
            FIRST_VALUE(ts) OVER (PARTITION BY id ORDER BY ts) AS start_ts,
            LAST_VALUE(ts) OVER (
                PARTITION BY id
                ORDER BY ts
                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
            ) AS last_ts,
            AVG(reasoning_len) OVER (PARTITION BY id) AS avg_reasoning_len,
            SUM(reasoning_len) OVER (PARTITION BY id) AS total_reasoning_len
        FROM (
            SELECT
                id,
                ts,
                score,
                reasoning_len,
                ROW_NUMBER() OVER (PARTITION BY id ORDER BY ts) AS attempt_n,
                SUM(score) OVER (
                    PARTITION BY id
                    ORDER BY ts
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                ) AS passed_so_far
            FROM (
                SELECT
                    participants.purple_agent::VARCHAR AS id,
                    r.unnest.score::INT AS score,
                    r.unnest.timestamp::TIMESTAMP AS ts,
                    LENGTH(r.unnest.metadata.raw_response) AS reasoning_len
                FROM results
                CROSS JOIN UNNEST(results) AS r(unnest)
                WHERE r.unnest.artifact_type = 'result'
            )
        )
    )
)
WHERE rnk = 1;