How can I structure a leaderboard aggregation query that scales?

[oalexdoda]

Howdy!

I'm trying to create a leaderboard that shows members ranked by a specific tracked metric.

For example, the number of leads they referred into a system.

This is the query I'm running:

    query LeaderboardLeads($since: timestamptz) @cached {
        affiliates_profiles_aggregate(
            order_by: { leads_aggregate: { count: desc } }
            where: { leads: {} }
        ) {
            nodes {
                member {
                    username
                    avatar
                    first_name
                    last_name
                }
                leads_aggregate(where: { created_at: { _gte: $since } }) {
                    aggregate {
                        count
                    }
                }
                id
            }
        }
    }

But I need it to accomplish a few things:

1. Only need the first 10 results (which can be achieved with the limit parameter)
2. Need to find a specific user's position on that leaderboard (i.e. if there was 100.000 people on the board, need to know the exact user position, i.e. if they're a specific rank)
3. Need to find their neighbors (the 2 above, and 2 below)

Any idea how to adjust this query (even if it means running multiple aggregations in one request) so that it achieves the items above at scale?

Found something similar with MongoDB, but trying to find a way to do it with Hasura / PostgreSQL:
https://stackoverflow.com/questions/49977356/leaderboard-rankings-on-large-collections/62402340#62402340

I don't want to pull an array of 100.000 elements to the client and then run a find and stuff like that because it's not exactly scalable.

Cheers!

[meetzaveri]

Hey @altechzilla

We did try to attempt what you are trying to solve here.

Here's the github repository, https://github.com/dventimihasura/hasura-projects/tree/master/leaderboard-1 where we have curated list of queries in README.md file

One of our team members has curated README.md file with all sorts of use cases that you can find them here - https://github.com/dventimihasura/hasura-projects/tree/master/leaderboard-1#what

We've covered enough use cases with query examples. I hope this helps.

Feel free to point out any questions or concerns, thanks!

[oalexdoda]

Brilliant @meetzaveri , I'll try it out asap and let you know. I really appreciate you for putting that together so quickly!