I have the following tables that make up a ranked leaderboard:
CREATE TABLE IF NOT EXISTS "user" (
"id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
"username" varchar(200) NOT NULL UNIQUE
);
CREATE TABLE IF NOT EXISTS "leaderboard" (
"id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
"name" varchar(200) NOT NULL UNIQUE
);
CREATE TABLE IF NOT EXISTS "leaderboard_entry" (
"id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
"score" integer unsigned NOT NULL CHECK ("score" >= 0),
"leaderboard_id" integer NOT NULL REFERENCES "leaderboard" ("id") DEFERRABLE INITIALLY DEFERRED,
"user_id" integer NOT NULL REFERENCES "user" ("id") DEFERRABLE INITIALLY DEFERRED
);
CREATE INDEX "score_idx" ON "leaderboard_entry" ("score" DESC);
CREATE UNIQUE INDEX "leaderboard_id_user_id_idx" ON "leaderboard_entry" ("leaderboard_id", "user_id");
CREATE INDEX "leaderboard_id_idx" ON "leaderboard_entry" ("leaderboard_id");
CREATE INDEX "user_id_idx" ON "leaderboard_entry" ("user_id");
-- Create a leaderboard
INSERT INTO "leaderboard" ("name") VALUES ('Global Leaderboard');
-- Create some users
INSERT INTO "user" ("username") VALUES ('Extreme Hawk');
INSERT INTO "user" ("username") VALUES ('Screaming Whistler');
INSERT INTO "user" ("username") VALUES ('Crashing Underdog');
INSERT INTO "user" ("username") VALUES ('Burly Creature');
INSERT INTO "user" ("username") VALUES ('Snarky Acrobat');
INSERT INTO "user" ("username") VALUES ('Deadly Striker');
INSERT INTO "user" ("username") VALUES ('Dark Zebra');
INSERT INTO "user" ("username") VALUES ('Eager Raptor');
INSERT INTO "user" ("username") VALUES ('Snarky Leader');
INSERT INTO "user" ("username") VALUES ('Keen Joker');
-- Add some leaderboard entries with random scores
INSERT INTO "leaderboard_entry" ("leaderboard_id", "user_id", "score") VALUES (1, 1, 15);
INSERT INTO "leaderboard_entry" ("leaderboard_id", "user_id", "score") VALUES (1, 2, 80);
INSERT INTO "leaderboard_entry" ("leaderboard_id", "user_id", "score") VALUES (1, 3, 45);
INSERT INTO "leaderboard_entry" ("leaderboard_id", "user_id", "score") VALUES (1, 4, 55);
INSERT INTO "leaderboard_entry" ("leaderboard_id", "user_id", "score") VALUES (1, 5, 95);
INSERT INTO "leaderboard_entry" ("leaderboard_id", "user_id", "score") VALUES (1, 6, 90);
INSERT INTO "leaderboard_entry" ("leaderboard_id", "user_id", "score") VALUES (1, 7, 90);
INSERT INTO "leaderboard_entry" ("leaderboard_id", "user_id", "score") VALUES (1, 8, 25);
INSERT INTO "leaderboard_entry" ("leaderboard_id", "user_id", "score") VALUES (1, 9, 60);
INSERT INTO "leaderboard_entry" ("leaderboard_id", "user_id", "score") VALUES (1, 10, 55);
I can rank a specific leaderboard with this SQL query:
SELECT "leaderboard_entry"."id",
"leaderboard_entry"."leaderboard_id",
"leaderboard_entry"."user_id",
"leaderboard_entry"."score",
RANK() OVER (PARTITION BY "leaderboard_entry"."leaderboard_id" ORDER BY "leaderboard_entry"."score" DESC) AS "rank",
PERCENT_RANK() OVER (PARTITION BY "leaderboard_entry"."leaderboard_id" ORDER BY "leaderboard_entry"."score" DESC) AS "percentile_rank",
"leaderboard"."id",
"leaderboard"."name",
"user"."id",
"user"."username"
FROM "leaderboard_entry"
INNER JOIN "leaderboard"
ON ("leaderboard_entry"."leaderboard_id" = "leaderboard"."id")
INNER JOIN "user"
ON ("leaderboard_entry"."user_id" = "user"."id")
WHERE "leaderboard_entry"."leaderboard_id" = 1
ORDER BY "leaderboard_entry"."score" DESC
This produces the correct results where every entry is properly ranked:
+----------------+--------------------+------+------------+-------+---------+--------------------+
| Leaderboard ID | Leaderboard Name | Rank | Percentile | Score | User ID | User Name |
+----------------+--------------------+------+------------+-------+---------+--------------------+
| 1 | Global Leaderboard | 1 | 0.000 | 95 | 5 | Snarky Acrobat |
| 1 | Global Leaderboard | 2 | 0.111 | 90 | 6 | Deadly Striker |
| 1 | Global Leaderboard | 2 | 0.111 | 90 | 7 | Dark Zebra |
| 1 | Global Leaderboard | 4 | 0.333 | 80 | 2 | Screaming Whistler |
| 1 | Global Leaderboard | 5 | 0.444 | 60 | 9 | Snarky Leader |
| 1 | Global Leaderboard | 6 | 0.556 | 55 | 4 | Burly Creature |
| 1 | Global Leaderboard | 6 | 0.556 | 55 | 10 | Keen Joker |
| 1 | Global Leaderboard | 8 | 0.778 | 45 | 3 | Crashing Underdog |
| 1 | Global Leaderboard | 9 | 0.889 | 25 | 8 | Eager Raptor |
| 1 | Global Leaderboard | 10 | 1.000 | 15 | 1 | Extreme Hawk |
+----------------+--------------------+------+------------+-------+---------+--------------------+
However, I am unable to query for a specific User ID in order to know their rank in the leaderboard. It always says they're ranked 1. I assume this is because the filter for User ID is being applied before the RANK() window function. How can I do the query so that it returns the correct rank for a specific User ID?
This doesn't work:
SELECT "leaderboard_entry"."id",
"leaderboard_entry"."leaderboard_id",
"leaderboard_entry"."user_id",
"leaderboard_entry"."score",
RANK() OVER (PARTITION BY "leaderboard_entry"."leaderboard_id" ORDER BY "leaderboard_entry"."score" DESC) AS "rank",
PERCENT_RANK() OVER (PARTITION BY "leaderboard_entry"."leaderboard_id" ORDER BY "leaderboard_entry"."score" DESC) AS "percentile_rank",
"leaderboard"."id",
"leaderboard"."name",
"user"."id",
"user"."username"
FROM "leaderboard_entry"
INNER JOIN "leaderboard"
ON ("leaderboard_entry"."leaderboard_id" = "leaderboard"."id")
INNER JOIN "user"
ON ("leaderboard_entry"."user_id" = "user"."id")
WHERE ("leaderboard_entry"."user_id" = 3 AND "leaderboard_entry"."leaderboard_id" = 1)
ORDER BY "leaderboard_entry"."score" DESC
+----------------+--------------------+------+------------+-------+---------+-------------------+
| Leaderboard ID | Leaderboard Name | Rank | Percentile | Score | User ID | User Name |
+----------------+--------------------+------+------------+-------+---------+-------------------+
| 1 | Global Leaderboard | 1 | 0.000 | 45 | 3 | Crashing Underdog |
+----------------+--------------------+------+------------+-------+---------+-------------------+
The correct rank for User ID 3 should be 8, not 1.
Additionally, I would like to be able to filter for a specific User ID and return the entries "around" that user on the leaderboard. So if a User is ranked 5, and I want to show the 4 entries around them, I would query for the User ID and also select the 2 rows ahead of them, and the 2 rows behind them.
Any help is appreciated. Thank you!
Window functions operate on the resulting dataset, after filtering with the where predicates. So there is just one record left in the dataset after filtering, it will always be ranked first.
You would need to turn your existing query to a subquery, then filter on the given user:
SELECT *
FROM (
SELECT "leaderboard_entry"."id",
"leaderboard_entry"."leaderboard_id",
"leaderboard_entry"."user_id",
"leaderboard_entry"."score",
RANK() OVER (PARTITION BY "leaderboard_entry"."leaderboard_id" ORDER BY "leaderboard_entry"."score" DESC) AS "rank",
PERCENT_RANK() OVER (PARTITION BY "leaderboard_entry"."leaderboard_id" ORDER BY "leaderboard_entry"."score" DESC) AS "percentile_rank",
"leaderboard"."id",
"leaderboard"."name",
"user"."id",
"user"."username"
FROM "leaderboard_entry"
INNER JOIN "leaderboard"
ON ("leaderboard_entry"."leaderboard_id" = "leaderboard"."id")
INNER JOIN "user"
ON ("leaderboard_entry"."user_id" = "user"."id")
WHERE "leaderboard_entry"."leaderboard_id" = 1
) t
WHERE "user_id" = 3
Note that the ORDER BY clause is not needed anymore, since the query returns just one row. Else, you would need to move it to the outer query too.