If you're building something like a leaderboard, a fitness tracker, or any competitive feature, ranking users by their score is something you'll run into pretty quickly. It sounds simple — "just sort them and pick the position" — but when you get into Django ORM, there are actually a few different ways to do this, and they're not all equal. Let me walk you through all of them, explain how each one works, and tell you which one I'd actually use in production.
The Setup
Let's say you have a User model with an id, name, and points field. Something like this:
| id | name | points |
|---|---|---|
| 1 | Alice | 150 |
| 2 | Bob | 200 |
| 3 | Carol | 100 |
Bob has the highest points, so he should be ranked 1st. Alice is 2nd, Carol is 3rd. Simple enough in concept — now let's look at the different ways to get there in code.
Approach 1: Window Functions (The Proper Way)
Django has had support for window functions since version 2.0, and if you're not using them, you're missing out. This is the cleanest, most scalable solution.
from django.db.models import F, Window
from django.db.models.functions import Rank
users_with_rank = User.objects.annotate(
rank=Window(
expression=Rank(),
order_by=F('points').desc()
)
)
# Get Bob's rank
bob_rank = users_with_rank.get(id=2).rank
print(f"Bob's Rank: {bob_rank}") # Bob's Rank: 1
This runs a single SQL query and calculates the rank for every user in one shot. The Window class tells Django to use a SQL window function, Rank() handles the ranking logic, and order_by=F('points').desc() makes sure the person with the most points gets rank 1.
One thing to know about Rank(): it handles ties the same way SQL's RANK() function does — if two users have the same points, they get the same rank, and the next rank is skipped. So if Alice and Bob both had 200 points, they'd both be rank 1, and Carol would jump to rank 3 (no rank 2).
If you don't want that gap, use DenseRank() instead:
from django.db.models.functions import DenseRank
users_with_rank = User.objects.annotate(
rank=Window(
expression=DenseRank(),
order_by=F('points').desc()
)
)
With DenseRank(), tied users both get rank 1, and the next user gets rank 2 — no skipped numbers.
Approach 2: Subquery / Count Filter (Quick and Simple)
This one is easy to understand and works without any special imports. You count how many users have more points than the user you're looking at, then add 1.
specific_user = User.objects.get(id=2) # Bob
user_rank = User.objects.filter(points__gt=specific_user.points).count() + 1
print(f"Bob's Rank: {user_rank}") # Bob's Rank: 1
This makes two queries — one to fetch Bob, one to count users above him. For a small table, that's totally fine. The problem is it doesn't scale well. If you need ranks for 50 users, you'd run 51 queries. And it doesn't gracefully handle ties — if two users are tied, they'll both show different ranks depending on who you ask about first.
Use this approach when you need the rank of a single user on a small dataset and you want to keep the code dead simple.
Approach 3: Raw SQL
Sometimes you just want to drop into raw SQL and be done with it. Django lets you do that with raw():
users = User.objects.raw("""
SELECT id, name, points,
RANK() OVER (ORDER BY points DESC) AS rank
FROM myapp_user
""")
for user in users:
print(f"{user.name}: Rank {user.rank}")
This works, and it gives you full SQL flexibility. But you lose all the ORM benefits — no composability with other querysets, no easy filtering afterward, and you have to manually handle the table name. I'd only reach for this if the ORM genuinely can't express what you need, which is rare.
Approach 4: Python-Side Sorting
This one's not a database approach at all — you pull everyone into memory and sort in Python:
all_users = list(User.objects.order_by('-points'))
for index, user in enumerate(all_users, start=1):
user.computed_rank = index
# Find Bob
bob = next(u for u in all_users if u.id == 2)
print(f"Bob's Rank: {bob.computed_rank}") # Bob's Rank: 1
This is fine if you're already loading all users for other reasons (say, rendering a full leaderboard page). But if you're just doing this to find one person's rank, pulling the entire table into Python memory is wasteful. It also doesn't handle ties — everyone gets a unique rank even if scores are equal.
Approach 5: Caching the Rank (For High-Traffic Apps)
If your leaderboard gets hit thousands of times a minute and the scores don't change that often, computing the rank on every request is unnecessary load on your database. A common pattern is to compute ranks periodically and cache them:
from django.core.cache import cache
from django.db.models import F, Window
from django.db.models.functions import Rank
def get_cached_rankings():
cached = cache.get('user_rankings')
if cached:
return cached
users = User.objects.annotate(
rank=Window(
expression=Rank(),
order_by=F('points').desc()
)
).values('id', 'rank')
rankings = {u['id']: u['rank'] for u in users}
cache.set('user_rankings', rankings, timeout=300) # Cache for 5 minutes
return rankings
def get_user_rank(user_id):
rankings = get_cached_rankings()
return rankings.get(user_id)
This way, the expensive window function query only runs once every 5 minutes (or whatever interval makes sense for your app). Every other request just reads from cache. You can trigger a cache invalidation whenever a user's score changes if you need it to be more real-time.
Which Approach Should You Use?
Here's a quick breakdown:
| Approach | Best For | Watch Out For |
|---|---|---|
| Window Functions | Most cases — clean, scalable, handles ties properly | Requires Django 2.0+ (not an issue in practice today) |
| Count Filter | Single user rank, small dataset, simple code | Multiple queries, doesn't handle ties well |
| Raw SQL | Complex ranking logic the ORM can't express | No ORM composability, manual table names |
| Python Sorting | Already loading full list for other reasons | Memory usage, doesn't handle ties |
| Caching | High-traffic apps with infrequent score changes | Stale data between cache refreshes |
Honestly, for 90% of use cases, window functions are the right answer. They run a single efficient query, the database handles all the logic, ties are handled correctly, and the code is readable. The only time I'd deviate is if I need real-time ranks on a very high-traffic endpoint — in that case, I'd combine window functions with caching.
The count filter approach is fine for a quick prototype or a low-traffic admin page. Raw SQL is a last resort. Python sorting is situational. And caching is a performance optimization you layer on top of whichever database approach you pick.
Wrapping Up
Ranking users in Django isn't complicated once you know your options. Start with window functions — they're what the database is built for and Django exposes them cleanly. If you run into performance issues at scale, add caching on top. And if you're prototyping quickly, the count filter approach will get you there in two lines.
The main thing to decide early is how you want to handle ties, because that choice shapes which function you reach for. Get that right upfront and the rest is straightforward.
Top comments (0)