Uma query por usuário, só para ranqueá-los

O Enrollment::UserGamificationQuery calcula um número que esta aplicação mostra a cada aluno: leaderboard_percentile, como o XP dele se compara ao de todos os outros. Até uma correção sair, no dia em que este curso foi escrito, o código que o calculava era este:

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

Este é o SQL que ele gerava, capturado do log de queries desta aplicação antes da correção:

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)

Essa linha tinha dois custos. SELECT "users".* puxava todas as colunas de todo usuário com mais XP. O lado Ruby só chamava .to_a.size no resultado: contava quantos voltaram, depois de carregar cada um por inteiro. E a query inteira rodava uma vez por chamada a UserGamificationQuery.call(user:), com o total_xp daquele aluno embutido na cláusula HAVING. Uma página de leaderboard com todos os alunos significaria rodá-la uma vez por linha da página.

O que o GROUP BY nunca foi feito para responder

O curso de nível Júnior "SQL: agregações" trata da ferramenta que essa query usava: GROUP BY, HAVING e SUM, que reduzem muitas linhas a um número por grupo. A pergunta que o leaderboard_percentile precisa responder tem outra forma. Não é "quantas linhas há neste grupo?", e sim "onde esta linha fica entre todas as outras, sem que nenhuma seja reduzida?"

O GROUP BY não consegue responder isso. Ele sempre produz menos linhas do que recebeu, uma por grupo, e um percentil precisa que a linha de cada aluno continue existindo depois, cada uma com a própria resposta.

O recurso de que este curso trata

Para essa forma de pergunta, comparar cada linha a um grupo sem reduzir nada, o Postgres tem uma ferramenta própria: a função de janela. A cláusula que faz de uma função uma função de janela é OVER. A gambiarra de percentil desta aplicação é o exemplo que o curso reconstrói, do jeito certo, nas próximas aulas: uma query que calcula o percentil de um aluno, sem carregar linhas inteiras de usuário só para contá-las.

Atualização: corrigido no mesmo dia, depois substituído

O Enrollment::LeaderboardRankQuery, acrescentado no commit e16a0d3d em 2 de setembro de 2026, calculava a posição por trás do leaderboard_percentile numa única query. Uma CTE WITH totals AS (...) alimentava RANK() OVER (ORDER BY total_xp DESC), a forma que as aulas 2 e 4 constroem.

Ela usava RANK(), e não PERCENT_RANK(). rank - 1 é a mesma contagem que a query antiga com HAVING produzia, empates incluídos, então os percentis que os alunos já viam não mudaram; só mudou o jeito de calculá-los. A query filtrava um aluno (WHERE user_id = :user_id). Ela não calculava todos de uma vez porque a aplicação ainda não tinha página de listagem de leaderboard. Se uma fosse construída, usaria a mesma query sem esse filtro.

Essa página agora existe, e a query foi substituída. O Enrollment::RefreshLeaderboardJob ranqueia todos os alunos a cada 15 minutos com um único INSERT ... SELECT: uma CTE de totais alimentando RANK() OVER (ORDER BY xp DESC), mais um ROW_NUMBER() com critério de desempate para a ordem de exibição, guardado em snapshots. O UserGamificationQuery lê a posição do aluno no snapshot atual e a entrega ao Enrollment::LeaderboardPercentile, que mantém a regra do rank - 1 descrita aqui. A função de janela não sumiu. Ela saiu do caminho da requisição e agora responde por todos de uma vez.

Pontos-chave

• O UserGamificationQuery calculava o leaderboard_percentile com uma query GROUP BY/HAVING por aluno, carregando por inteiro cada linha que contava e depois descartando as linhas. O log de queries mostrou isso, e a correção saiu no dia em que este curso foi escrito.
• "SQL: agregações" trata do que o GROUP BY faz: reduzir muitas linhas a uma linha por grupo. Um percentil precisa do contrário, todas as linhas ainda presentes, cada uma com uma resposta calculada sobre o lugar dela entre as outras.
• Uma função de janela, marcada pela cláusula OVER, é a ferramenta do Postgres para isso: comparar uma linha a um grupo sem reduzir o grupo.
• O Enrollment::LeaderboardRankQuery fez isso primeiro, usando RANK() para manter os percentis exatos que os alunos já viam, filtrado a um aluno. Um job agendado o substituiu, ranqueando todos os alunos num INSERT ... SELECT a cada 15 minutos.