How to build a leaderboard in Django that allows for arbitrary querying and slicing?

Question

I have a basic leaderboard in Django 2 that looks like this:

from django.db import models


class Leaderboard(models.Model):

    username = models.CharField(max_length=200)
    score = models.PositiveIntegerField()

    class Meta:
        indexes = [models.Index(fields=["-score"])]

    def __str__(self):
        return self.username

I can produce a ranking of this leaderboard table using Django's built-in window functions Rank() and PercentRank():

from django.db.models import F, Window
from django.db.models.functions import PercentRank, Rank


entries = (
    Leaderboard.objects.annotate(
        rank=Window(
            expression=Rank(),
            order_by=F("score").desc(),
        ),
        percentile=Window(
            expression=PercentRank(),
            order_by=F("score").desc(),
        ),
    )
)

print("Rank\tPercentile\tScore\tUser ID\tUser Name")
for entry in entries:
    print(f"{entry.rank}\t{entry.percentile:.3f}\t{entry.score}\t{entry.id}\t{entry.username}")

This produces a pageable table with the rankings:

    Rank    Percentile  Score   User ID User Name
    1   0.000   1000    564 Eager Guard
    2   0.001   999 302 Top Hawk
    2   0.001   999 747 Eager Whistler
    2   0.001   999 842 Extreme Legend
    5   0.004   997 123 Witty Guard
    5   0.004   997 201 Arctic Elephant
    7   0.006   996 21  Speedy Bear
    7   0.006   996 678 Agile Player
    9   0.008   995 562 Fast Hawk
    10  0.009   994 467 Loyal Legend

However, I am having difficulty querying for a specific User ID in the leaderboard and retrieving their rank and percentile. It seems that the result of the window functions are only being applied within the current queryset (ie, limited to the filter for the User ID) instead of across the whole leaderboard set:

entry = (
    Leaderboard.objects.filter(id=100).annotate(
        rank=Window(
            expression=Rank(),
            order_by=F("score").desc(),
        ),
        percentile=Window(
            expression=PercentRank(),
            order_by=F("score").desc(),
        ),
    )
    .first()
)

Rank    Percentile  Score   User ID User Name
1   0.000   876 100 Clear Star

The correct rank for this user should be:

Rank    Percentile  Score   User ID User Name
135 0.134   876 100 Clear Star

So the questions are:

1) How to query for a specific User ID and get their correct rank/percentile on the leaderboard?

2) How to query for a "slice" of the leaderboard? For instance, given User ID=100, how to query for their entry and +/- 5 entries around them? So if they're ranked 135 on the leaderboard, how to query for the 10 entries around them?

Postgres is the production database but I would like to keep it RDBMS agnostic, if possible.

Any help is appreciated! Thanks!

EDIT:

I have managed to answer my first question (how to query for specific leaderboard entry by User ID) with using the Raw SQL API:

SINGLE_ENTRY_SQL = """
SELECT
    id,
    username,
    score,
    rank,
    percentile_rank
FROM (
  SELECT
    U0.id,
    U0.username,
    U0.score,
    RANK() OVER (ORDER BY U0.score DESC) AS rank,
    PERCENT_RANK() OVER (ORDER BY U0.score DESC) AS percentile_rank
  FROM (
    SELECT
      id,
      username,
      score
    FROM leaderboard
  ) AS U0
)
WHERE
    id = %s
"""

# Query for leaderboard entry where User ID=100
entry = Leaderboard.objects.raw(SINGLE_ENTRY_SQL, [100])[0]

This now produces the correct result for a single entry:

Rank    Percentile  Score   User ID User Name
135 0.134 876 100 Clear Star

However, I'm still having difficulty querying for a "slice" of the leaderboard given a specific User ID.

How to build a leaderboard in Django that allows for arbitrary querying and slicing?

0 Answers0