-- InfiniSynapse RANK SQL desk microbench fixture
-- Marker: DESK-RANK-20260814A
-- Engine target: PostgreSQL 16.x, single node
-- Not a PostgreSQL Inc. or Microsoft official benchmark and not a cloud SLA.
-- Method: SET work_mem='64MB'; SET max_parallel_workers_per_gather=0;
-- Warm the cache with one discarded run, then take the median of 5 EXPLAIN ANALYZE wall times.
-- Normalize RANK median = 1.00x at each scale. Re-run on your hardware before citing internally.

DROP TABLE IF EXISTS desk_scores;
CREATE TABLE desk_scores AS
SELECT
  gs AS id,
  (gs % 50) AS partition_id,
  (random() * 1000)::integer AS score
FROM generate_series(1, 1000000) AS gs;

VACUUM ANALYZE desk_scores;

-- Repeat after substituting generate_series(1, 10000) or (1, 100000) for other scales.

EXPLAIN (ANALYZE, BUFFERS)
SELECT partition_id, id, score,
       RANK() OVER (PARTITION BY partition_id ORDER BY score DESC) AS rnk
FROM desk_scores;

EXPLAIN (ANALYZE, BUFFERS)
SELECT partition_id, id, score,
       DENSE_RANK() OVER (PARTITION BY partition_id ORDER BY score DESC) AS rnk
FROM desk_scores;

EXPLAIN (ANALYZE, BUFFERS)
SELECT partition_id, id, score,
       ROW_NUMBER() OVER (PARTITION BY partition_id ORDER BY score DESC, id) AS rnk
FROM desk_scores;
