CREATE TABLE bench (cpu text, var1ndigits int, version text, time numeric);

\COPY bench FROM bench.csv WITH CSV HEADER;

WITH ranked_bench AS (
    SELECT
        cpu,
        var1ndigits,
        version,
        ROW_NUMBER() OVER (PARTITION BY cpu, var1ndigits ORDER BY AVG(time)) AS rn
    FROM bench
    GROUP BY cpu, var1ndigits, version
)
SELECT
    cpu,
    var1ndigits,
    version AS winner
FROM ranked_bench
WHERE rn = 1
ORDER BY cpu, var1ndigits;


WITH avg_times AS (
    SELECT
        cpu,
        var1ndigits,
        version,
        AVG(time) AS avg_time
    FROM bench
    GROUP BY cpu, var1ndigits, version
),
head_times AS (
    SELECT
        cpu,
        var1ndigits,
        avg_time AS head_avg_time
    FROM avg_times
    WHERE version = 'HEAD'
)
SELECT
    a.cpu,
    a.var1ndigits,
    a.version,
    ROUND(h.head_avg_time / a.avg_time,2) AS performance_ratio
FROM avg_times a
JOIN head_times h
ON a.cpu = h.cpu AND a.var1ndigits = h.var1ndigits
ORDER BY a.cpu, a.var1ndigits, a.version;