One query per user, just to rank them

Enrollment::UserGamificationQuery computes a number this app shows each learner: leaderboard_percentile, how their XP stacks up against everyone else's. Until a fix shipped on the day this course was written, the code that computed it read:

users_with_more_xp = ::Identity::User.joins(:experience_points)
                                     .group("users.id")
                                     .having("SUM(experience_points.amount) > ?", total_xp).to_a.size
percentile = [ ((users_with_more_xp.to_f / total_users) * 100).ceil, 1 ].max

Here's the SQL it generated, captured from this app's query log before the fix:

SELECT "users".* FROM "users"
  INNER JOIN "experience_points" ON "experience_points"."user_id" = "users"."id"
  GROUP BY "users"."id"
  HAVING (SUM(experience_points.amount) > 640)

That one line had two costs. SELECT "users".* pulled every column of every user with more XP, when the Ruby side only called .to_a.size on the result: it counted how many came back, after loading each one in full. And the whole query ran once per call to UserGamificationQuery.call(user:), with that one learner's total_xp baked into the HAVING clause. A leaderboard page ranking every learner would have meant running it once per row on the page.

What GROUP BY was never built to answer

The junior course "SQL: grouping & aggregates" covers the tool this query reached for: GROUP BY, HAVING and SUM, which collapse many rows into one number per group. The question leaderboard_percentile needs answered has a different shape. It's not "how many rows are in this group?" but "where does this one row sit among all the others, with none of them collapsed?"

GROUP BY can't answer that at all. It only ever produces fewer rows than it started with, one per group, and a percentile needs every learner's row to still exist afterwards, each carrying its own answer.

The feature this course is about

Postgres has a purpose-built answer to that shape of question, comparing each row to a group of rows without collapsing anything: the window function. The clause that makes a function a window function is OVER. This app's percentile hack is the running example the course rebuilds, correctly, in the lessons ahead: one query that computes a learner's percentile, with no full user rows loaded just to be counted.

Update: fixed the same day, then replaced

Enrollment::LeaderboardRankQuery, added in commit e16a0d3d on 2 September 2026, computed the rank behind leaderboard_percentile in a single query. A WITH totals AS (...) CTE fed RANK() OVER (ORDER BY total_xp DESC), the shape lessons 2 and 4 build.

It used RANK(), not PERCENT_RANK(). rank - 1 is the same count the old HAVING query produced, ties included, so the percentiles learners already saw didn't change; only the way they were computed did. The query filtered to one learner (WHERE user_id = :user_id) and didn't compute everyone at once, because the app had no leaderboard listing page then. A listing page, if one were built, would have used the same query without that filter.

That page now exists, and the query has been replaced. Enrollment::RefreshLeaderboardJob ranks every learner every 15 minutes with one INSERT ... SELECT: a totals CTE feeding RANK() OVER (ORDER BY xp DESC), plus a ROW_NUMBER() with a tiebreaker for display order, stored as snapshots. UserGamificationQuery reads the learner's rank from the current snapshot and hands it to Enrollment::LeaderboardPercentile, which keeps the rank - 1 rule described here. The window function didn't go away. It moved off the request path and now answers for everyone at once.

Key takeaways

• UserGamificationQuery used to compute leaderboard_percentile with a GROUP BY/HAVING query per learner, loading every row it counted in full and then discarding the rows. The query log showed it, and it was fixed the day this course was written.
• "SQL: grouping & aggregates" covers what GROUP BY is for: collapsing many rows into one row per group. A percentile needs the opposite, every row still present, each carrying a computed answer about its place among the others.
• A window function, marked by the OVER clause, is Postgres's dedicated tool for that: comparing a row to a group without collapsing the group.
• Enrollment::LeaderboardRankQuery first did this, using RANK() to keep the exact percentiles learners already saw, filtered to one learner. A scheduled job has since replaced it, ranking every learner in one INSERT ... SELECT every 15 minutes.