Hi Everyone, I’m trying to build a real-time leaderboard and found Apache Pinot, but I’m not really sure it can solve the entire problem. Any help would be appreciated! I’m really stuck in this problem 😞 Problem: I need to build a real-time leaderboard that rank users by the avg of scores from a “PostScore” table and can return some statistics like: number of users in the ranking, position of each user in the ranking, filter by post categories, post skills (these two should affect the sum of scores as well, it’s like having one ranking for each filter possibility), filter by user age, user country and finally, computing only the scores given in the last 24 hours or all-time. is it possible with Apache Pinot?

Are you looking to get all those answered in one query?

Right now I’m doing all the work in the code, I’m not sure if it’s a good solution because I don’t have too much experience with these kind of problems --- A post can be rated from 1 to 10 points “PostRatedEvent”

json
{
  "postId": "1",
  "rating": 10
}

For each “PostRated” event, I’m doing an incremental calculation to a table storing a json like this to get a “FinalScore” of the post and some other values necessary to build the user rank: “PostScore”

json
{
  "avg": 0,
  "oldAvg": 0,
  "post": {
    "postId": "1",
    "userId": "2",
    "createdAt": "2022-10-10T00:00:00.000Z"
  },
  "count": 0,
  "avgScore": 0,
  "finalScore": 0,
  "karmaRange": { "max": 10, "min": 0 },
  "newVariance": 0,
  "oldVariance": 0,
  "cntEachRating": [0, 0, 0, 0, 0, 0, 0, 0, 0, 0],
  "sumScoreEachRating": [0, 0, 0, 0, 0, 0, 0, 0, 0, 0],
  "avgRatingsWithinKarma": 0,
  "cntRatingsWithinKarma": 0,
  "isConsideredForRanking": false
}

I did it in this way because of the requirement to calculate the “FinalScore” based on standard deviation and some other rules. So I always store some aditional values that helps me calculate the next score without querying all ratings to get the standard deviation and etc... For the user scores I’m doing something similar to calculate the scores for each user based on the requirements and I always keep a document like this for each user: “UserScore”

json
{
  "avgs": {
    "avgStd": 0,
    "avgValidRating": 0,
    "avgValidRatingQty": 0,
    "avgAgeConsideredPosts": 0,
    "avgScoreConsideredPosts": 0,
    "avgAgeScoreConsideredPosts": 0,
    "avgFinalScoreConsideredPosts": 0
  },
  "userId": "1",
  "finalScore": 0,
  "validPostsQuantity": 0
}

Finally, what I currently have is a query that ranks the top 100 users by the “FinalScore” and can also search by the name to get the positions of users that name match a query param:

sql
# sample query

SELECT position, # and other fields from user FROM 
  (SELECT ROW_NUMBER() over (order by finalScore DESC) position, userId from UserScore limit 100) # ...
# INNER JOIN User...
# WHERE User.name like "%something%"

With this I can only get the User Ranking with name filtering, this is for only one requirement of the app. I’m not sure if this would work in production scale for millions of users (probably not). I’m trying to create different rankings for each type of dimensions like category, skill but with this approach I think would have to pre-calculate all possible user scores for each separate rank. It would be very complex for me and that’s why I’m searching for another solution I also have to create another query (or multiple queries) to build a table like this to display to the end user: Ex: -------------------------------------- Category | Country | All | -------------------------------------- | Brazil | World | ------------------------------------- All | 1° Pos | 20° pos | (10 users) (500 users)| -------------------------------------- Cat1 | 5° Pos | 5° pos | (10 users) (20 users) | -------------------------------------- - Position of a specific user in this ranking - Number of users inside an specific ranking (when filtering post dimensions like category and skills) --- When I found Apache Pinot, I though it could help me in some way. Maybe I could only use PostScore table to load information to Pinot and calculate the UserScore for each dimension in a query so I can rank it in real-time, but I’m not sure it is possible. Sorry if it’s very long. I really don’t know what to do.

thanks for sharing the details.. let me parse it

If I understand correctly, you could have a flat schema with post, raring, user, author, other dims, and emit that every time a post is scored. Then you can do most queries you have (with aggregation happening in Pinot). What might be missing is the std-dev computation etc, which can be added in Pinot too.

But is it possible to get specific ranking positions with pinot? Right now I’m using a subquery with a row_number window function to get it and what I found is that Pinot doesn’t support it yet. Is there another way of doing it?

If return response is small, you could do the ranking on client side for now.

But lets say I have millions of users and I want to display that table for them, with the positions in the ranking they are in each category and skills. Do I have to get the ranking for all the users and find where this specific user is?

You can get top N users from Pinot and do the rank on client in the subset of N. I assume you don’t have to do this for all millions of users but only top N?

Sadly I have to do this for all users

But all users for a given set of dimension filters or all of the table?

Actually both. I was looking for something like the Redis SortedSet but it only gives the rank for all users without being able to filter the dimensions, and I think I would have create a SortedSet for each possible Ranking

I think you'll need a pre-processing layer to do global rankings.

Note that ranks could also be for any user specified filter, which the preprocessing layer won’t know about (or it has to explode all cubes).

Is this preprocessing layer outside pinot? An example would be very helpful, thank you very much!

It would be. I'll follow up with an example when I get it together.