Leaderboard database table

[strager]

Redemption table:

• redemption_id
• channel_id
• stream_id
• user_id
• timestamp
• level (first, second, third)

Methods:

• add row (channel_id, user_id, level, ...)
• get top scores in past month (channel_id)
• get top lifetime scores (channel_id)
  • first=5 points, second=3 points, third=1 point
• get scores for user across channels (user_id)

[mate-amargo]

Redemption table:

                "CREATE TABLE IF NOT EXISTS "
                "redemptions("
                    "broadcaster_id, "
                    "reward_id UNIQUE, "
                    "user_id, "
                    "redeemed_at, "
                    "level"
                ")"

• [x] Add row
• [x] Get top scores in the past month
• [x] Get lifetime scores
• [x] Get scores across channels (monthly and lifetime)

[mate-amargo]

Done in commit 0925835. Some notes:

• Will this scale up? I'm adding all the points using the SUM call in sqlite, will that query be too slow when we have a big db? if so, how can we make it faster?
• Currently I only added methods for returning the total amount of points per user across all channels. We could also have a method returning a list of tuples containing the sum of points per streamer for a given user.

@strager thoughts?

[strager]

Will this scale up? I'm adding all the points using the SUM call in sqlite, will that query be too slow when we have a big db? if so, how can we make it faster?

It won't scale to a billion rows, but it should be fine with a million rows. 1 million redemptions / 3 redemptions per stream per day / 1 year = 913 streams

We could also have a method returning a list of tuples containing the sum of points per streamer for a given user.

I think this is a good idea, but not for MVP.