How to create continuous views for leaderboards?

Question

I have a set of events coming in with the structure player_id, score, timestamp. I want to create cycle based leaderboards on this so that I can see the players daily, weekly, monthly and yearly leaderboards. What kind of aggregations should I use. Could I use ordered set aggregates with rank?. And is it possible to also see/store the past/historical leaderboards so that I could also see last months leaderboards?

Answer 1

You can use fss_agg_weighted to build filtered space saving top-ks, and then extract the top-k player scores by calling fss_topk on the column built by fss_agg_weighted. For example, to continuously compute the daily top 10 player scores:

CREATE CONTINUOUS VIEW daily_top_scores AS
   SELECT day(timestamp), fss_agg_weighted(player_id, 10, score) GROUP BY day;

And to extract the top-10 at a given point in time,

SELECT day, fss_topk(fss_agg_weighted) FROM daily_top_scores;

You can also combine the top-k results over wider date ranges without losing any information. To compute the top-10 scores over the entire history of the continuous view:

SELECT fss_topk(combine(fss_agg_weighted)) FROM daily top_scores;