Automated Leaderboard Reset

Leaderboard Reset

Use events to snapshot weekly/monthly leaderboards and reset scores at regular intervals.

Example

CREATE EVENT weekly_leaderboard_reset
ON SCHEDULE EVERY 1 WEEK STARTS "2024-01-07 23:59:59"
DO
BEGIN
  -- Archive current leaderboard
  INSERT INTO leaderboard_history (user_id, score, week_start, week_end, rank_pos)
  SELECT user_id, score, DATE_SUB(CURDATE(), INTERVAL 6 DAY), CURDATE(),
    RANK() OVER (ORDER BY score DESC)
  FROM leaderboard;
  -- Reset scores for new week
  UPDATE leaderboard SET score = 0, games_played = 0;
  INSERT INTO event_audit (event_name, run_at) VALUES ("weekly_reset", NOW());
END;

SQL

Full Editor

Pro Tip

Use RANK() OVER (ORDER BY score DESC) to compute rankings without a PHP loop.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned