-- Kernelbot Database Queries
-- All queries are READ ONLY. Never run INSERT/UPDATE/DELETE on production.
-- Scores are execution time in seconds. Lower is better.

--------------------------------------------------------------------------------
-- LIST ALL PROBLEMS
--------------------------------------------------------------------------------
SELECT
    l.id,
    l.name,
    l.deadline,
    l.description,
    array_agg(g.gpu_type) as gpu_types
FROM leaderboard.leaderboard l
LEFT JOIN leaderboard.gpu_type g ON l.id = g.leaderboard_id
GROUP BY l.id, l.name, l.deadline, l.description
ORDER BY l.id;

--------------------------------------------------------------------------------
-- PROBLEM IDS
--------------------------------------------------------------------------------
-- NVFP4: 595 (gemv), 597 (gemm), 598 (dual_gemm), 697 (modal_dual_gemm), 730 (group_gemm)
-- AMD: 398 (identity), 399 (fp8-mm), 430 (moe), 463 (mla-decode),
--      563 (all2all), 564 (gemm-rs), 565 (ag-gemm), 763 (mxfp4-mm),
--      764 (moe-mxfp4), 765 (mixed-mla)
-- Separate mixed-GPU export: 496 (trimul)
-- Released Helion/B200_Nebius export: 766 (causal_conv1d), 767 (fp8_quant),
--      768 (gated_deltanet_chunk_fwd_h), 769 (gated_deltanet_chunk_fwd_o),
--      770 (gated_deltanet_recompute_w_u)
-- Linear algebra export: 774 (qr_v2), 775 (eigh), 776 (cholesky)

--------------------------------------------------------------------------------
-- CHECK SUBMISSION COUNTS FOR A PROBLEM
--------------------------------------------------------------------------------
SELECT
    COUNT(*) as total_submissions,
    COUNT(DISTINCT user_id) as unique_users
FROM leaderboard.submission
WHERE leaderboard_id = 595;  -- Replace with problem ID

--------------------------------------------------------------------------------
-- EXPORT DEDUPLICATED SUBMISSIONS WITH CODE
-- Deduplicates by (user_id, code_id), keeping the fastest score
--------------------------------------------------------------------------------
WITH ranked AS (
    SELECT
        s.id as submission_id,
        s.leaderboard_id,
        l.name as problem_name,
        s.user_id,
        u.user_name,
        s.code_id,
        s.file_name,
        s.submission_time,
        s.status,
        r.score,
        r.passed,
        r.mode,
        r.runner,
        COALESCE(c.old_code, convert_from(c.code, 'UTF8')) as code,
        ROW_NUMBER() OVER (
            PARTITION BY s.leaderboard_id, s.user_id, s.code_id
            ORDER BY r.score ASC NULLS LAST
        ) as rn
    FROM leaderboard.submission s
    JOIN leaderboard.leaderboard l ON s.leaderboard_id = l.id
    LEFT JOIN leaderboard.user_info u ON s.user_id = u.id
    LEFT JOIN leaderboard.runs r ON s.id = r.submission_id AND r.mode = 'leaderboard'
    LEFT JOIN leaderboard.code_files c ON s.code_id = c.id
    WHERE s.leaderboard_id IN (595, 597, 598)  -- Replace with problem IDs
)
SELECT
    submission_id, leaderboard_id, problem_name, user_id, user_name,
    code_id, file_name, submission_time, status, score, passed, mode, runner, code
FROM ranked
WHERE rn = 1
ORDER BY problem_name, score ASC NULLS LAST;

--------------------------------------------------------------------------------
-- CHECK RUN MODES AND SCORES
--------------------------------------------------------------------------------
SELECT
    r.mode,
    COUNT(*) as cnt,
    COUNT(r.score) as has_score,
    MIN(r.score) as min_score,
    MAX(r.score) as max_score
FROM leaderboard.runs r
JOIN leaderboard.submission s ON r.submission_id = s.id
WHERE s.leaderboard_id IN (595, 597, 598)
GROUP BY r.mode
ORDER BY cnt DESC;

--------------------------------------------------------------------------------
-- GET TOP N SUBMISSIONS FOR A PROBLEM
--------------------------------------------------------------------------------
SELECT
    u.user_name,
    r.score,
    s.submission_time
FROM leaderboard.submission s
JOIN leaderboard.runs r ON s.id = r.submission_id AND r.mode = 'leaderboard'
LEFT JOIN leaderboard.user_info u ON s.user_id = u.id
WHERE s.leaderboard_id = 595  -- Replace with problem ID
  AND r.passed = true
  AND r.score IS NOT NULL
ORDER BY r.score ASC
LIMIT 20;

--------------------------------------------------------------------------------
-- GET USER'S SUBMISSIONS OVER TIME (progression)
--------------------------------------------------------------------------------
SELECT
    s.submission_time,
    r.score,
    r.passed
FROM leaderboard.submission s
JOIN leaderboard.runs r ON s.id = r.submission_id AND r.mode = 'leaderboard'
JOIN leaderboard.user_info u ON s.user_id = u.id
WHERE u.user_name = 'gau.nernst'  -- Replace with username
  AND s.leaderboard_id = 595  -- Replace with problem ID
ORDER BY s.submission_time ASC;

--------------------------------------------------------------------------------
-- GET CODE FOR A SPECIFIC SUBMISSION
--------------------------------------------------------------------------------
SELECT
    COALESCE(c.old_code, convert_from(c.code, 'UTF8')) as code
FROM leaderboard.code_files c
WHERE c.id = 79741;  -- Replace with code_id
