Recipes · 6 of 6

Leaderboards — top-N with score updates

Schema

scores: {user_id, name, score, rank, updated_at}
        # index on score (desc) for top-N queries

award_points

proc award_points(user_id, delta) {
    let s = find_one("scores", {user_id: user_id})
    if s == null {
        insert("scores", {user_id: user_id, score: delta})
        return {created: true, score: delta}
    }
    update("scores", {user_id: user_id}, {$inc: {score: delta}})
    return {created: false, score: s.score + delta}
}

top_n

proc top_n(n) {
    return aggregate("scores", [
        {$sort: {score: -1}},
        {$limit: n}
    ])
}

my_rank

proc my_rank(user_id) {
    let me = find_one("scores", {user_id: user_id})
    if me == null { return {rank: null} }
    let above = count("scores", {score: {$gt: me.score}})
    return {user_id: user_id, score: me.score, rank: above + 1}
}

around_me

proc around_me(user_id, window) {
    let me = find_one("scores", {user_id: user_id})
    if me == null { abort "no score" }
    return aggregate("scores", [
        {$match: {score: {$gte: me.score - window, $lte: me.score + window}}},
        {$sort: {score: -1}},
        {$limit: 25}
    ])
}

weekly_reset

proc weekly_reset() {
    let snapshot = aggregate("scores", [
        {$sort: {score: -1}},
        {$limit: 100}
    ])
    for entry in snapshot {
        insert("weekly_history", {
            user_id: entry.user_id, score: entry.score, week: "current"
        })
    }
    update("scores", {}, {$set: {score: 0}})
    return {ok: true, archived: 100}
}

award_with_event

proc award_with_event(user_id, points, source) {
    let r = award_points({user_id: user_id, delta: points})
    insert("score_events", {
        user_id: user_id, points: points, source: source, total_after: r.score
    })
    return r
}

category_top

proc category_top(category, n) {
    return aggregate("scores", [
        {$match: {category: category}},
        {$sort: {score: -1}},
        {$limit: n}
    ])
}