SELECT *
FROM (
SELECT s.*, ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
FROM snips s
WHERE created_at >= NOW() - INTERVAL '1 day'
) ranked
WHERE rank <= 3;
class Leaderboards::DailyTop
SQL = <<~SQL
SELECT *
FROM (
SELECT s.*, ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
FROM snips s
WHERE created_at >= NOW() - INTERVAL '1 day'
) ranked
WHERE rank <= 3
SQL
def call
ApplicationRecord.connection.exec_query(SQL).to_a
end
end
For leaderboards, let the database do ranking. Window functions are fast and expressive. Use them to compute daily top N without Ruby loops.
