Here is a tricky one to figure out for our SQL gurus: a badge for being exceptionally relevant. How to measure this? I suggest calculating the ratio of posts with at least one like or bookmark to the total number of posts (by that user).
So far, so good. But there are quite some variables that need tweaking for this to work.
To start with, this only makes sense for users with a certain minimum number of posts. So, let’s say users with less than 10 posts don’t qualify at all. This number might have to be much higher in order to have sufficient variation (i. e. posts that are not liked).
Next: what time period are we looking at? Perhaps this badge doesn’t need a time frame but could be calculated across all posts the user ever made? I’ll leave that open fof the time being.
Then there should probably be a requirement that the users post are spread across several categories or at least several topics. So let’s say, users whose posts live in less than 5 topics are not eligible.
For each eligible user we calculate the relevance ratio. Looking at that distribution, we take the top 33 percent (another figure that probably needs tweaking) and anyone whose ratio is higher than that receives the badge (if it’s a lifetime achievement, then it should be revoked if the conditions no longer apply. If it’s an achievement in a certain time period, it could be awarded multiple times.)
It might also make sense to set the threshold ratio as a fixed value via a site setting.