How to query Firebase Realtime Database for top N users and current user’s rank

Question

How to index Firebase Realtime Database for leaderboard and get current user’s rank?

I’m using Firebase Realtime Database to store user data for a game. I want to implement a leaderboard based on the highest score.

My current database structure looks like this:

{
  "users": {
    "Guest0009f30": {
      "levelContainer": {
        "currentLevel": 12,
        "highestScore": 1938
      }
    },
    "Guest0009f80": {
      "levelContainer": {
        "currentLevel": 16,
        "highestScore": 1894
      }
    }
  }
}

What I want to achieve

Retrieve the top 10 users ordered by highestScore.
Retrieve the current user’s rank in the leaderboard (even if they are not in the top 10).

What I’ve tried

I added an index rule:

{
  "rules": {
    "users": {
      ".read": true,
      ".write": true,
      "$uid": {
        ".indexOn": ["levelContainer/highestScore"]
      }
    }
  }
}

And my query in C#:

var task = _reference.Child("users")
    .OrderByChild("levelContainer/highestScore")
    .LimitToLast(20)
    .GetValueAsync();

This gives me the top 20 users, but I cannot figure out how to get the current user’s position/rank if they are not in the top 20.

Question

What is the correct way to index my database for this leaderboard use case?
How can I query Firebase Realtime Database to get both:
- The top N users, and
- The current user’s rank, even if they are outside the top N?

As for select query iself you can do select levelContainer/highestScore from myTable t order by case when t.user=currentUser then 0 else 1 end, levelContainer/highestScore desc. — ValNik
– ValNik, Commented Oct 17 at 17:14
@ValNik: OP uses Firebase Realtime Database, which is a NoSQL database. Your query looks like SQL, which Firestore doesn't support. — Frank van Puffelen
– Frank van Puffelen, Commented Oct 17 at 17:20
@FrankvanPuffelen, I mean something similar to ``.OrderByChild(... if (user=currentUser)?0:1) — ValNik
– ValNik, Commented Oct 17 at 19:25

Frank van Puffelen · Accepted Answer · 2025-10-17 19:03:35Z

The Firebase Realtime Database hasn't significantly changed in about a decade now, and both these topics have been covered extensively in the past. So while I'm writing up an answer here for your specific post, I'll also link to older posts as I find them.

Defining an index to allow getting the top N users

Getting the top N users is quite easy as you've already shown in your question. The only mistake is in your rules, as you need to define the index on the location where you run the query - which is one level up from where you currently have it:

{
  "rules": {
    "users": {
      ...
      ".indexOn": ["levelContainer/highestScore"]
    }
  }
}

Getting the ranking of a specific user

To determine the rank of a specific user requires that you read all users before that user. So you'd need to do a query that starts the current user's highest score:

var task = _reference.Child("users")
    .OrderByChild("levelContainer/highestScore")
    .StartAt(highestScoreOfCurrentUser, "currentUserUid")
    .GetValueAsync();

There really is no other way for this, and that is indeed an expensive operation ad you have to read all the preceding nodes to be able to count them. That's why (like most NoSQL databases) Firebase doesn't provide a native operation for it.

Use a database that supports this query natively

An alternative would be to use a database that does support this type of query. There are tons of those, just keep in mind that the query will be more CPU intensive as you get more players - which is precisely why NoSQL databases tend not to support it.

Use Firestore and a count query

Firestore nowadays has aggregation operations that can get you closer to what you need. If you store the highest score for each player, you can perform a count query similar to the one I showed for RTDB above. This will still have to scan the index for every document before the one of the current user, but doesn't read (or transfer) the actual data - so will be much cheaper.

Speeding up ranking reads by storing them

The common way to implement it is to store the user's ranking in the database, and then update it periodically or on demand. I'll find some links about this...

Using firebase functions to figure out the rank of every user at once (an example of recalculating rankings upon every write to RTDB)
Leaderboard ranking with Firebase (for Firestore, but the approach is pretty universal - and the author is an authoritative source for NoSQL databases)
Build leaderboards with Firestore (For Firestore, but the same logic of periodic updates also applies to RTDB)(This codelab implements some of the approaches described in the previous answer)
Fast and Reliable Ranking in Datastore (even older than the links above, but this is one of the most original articles I know of - implementing near-realtime leaderboards on Datastore, the precursor to Firestore)

Collectives™ on Stack Overflow

How to query Firebase Realtime Database for top N users and current user’s rank

How to index Firebase Realtime Database for leaderboard and get current user’s rank?

What I want to achieve

What I’ve tried

Question

1 Answer 1

Defining an index to allow getting the top N users

Getting the ranking of a specific user

Use a database that supports this query natively

Use Firestore and a count query

Speeding up ranking reads by storing them

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

How to index Firebase Realtime Database for leaderboard and get current user’s rank?

What I want to achieve

What I’ve tried

Question

1 Answer 1

Defining an index to allow getting the top N users

Getting the ranking of a specific user

Use a database that supports this query natively

Use Firestore and a count query

Speeding up ranking reads by storing them

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related