I’m facing an issue with a leaderboard query in my GraphQL API. The query is designed to fetch the highest WPM for each user, and in case of a tie, it should use accuracy and createdAt to determine the rank.

Here’s the query I’m using:

SELECT 
    "test"."id" AS "test_id", 
    "test"."creatorId" AS "test_creatorId", 
    "test"."time" AS "test_time", 
    "test"."accuracy" AS "test_accuracy", 
    "test"."wpm" AS "test_wpm", 
    "test"."rawWpm" AS "test_rawWpm", 
    "test"."chars" AS "test_chars", 
    "test"."createdAt" AS "test_createdAt", 
    "test"."testTaken" AS "test_testTaken", 
    "creator"."id" AS "creator_id", 
    "creator"."uid" AS "creator_uid", 
    "creator"."username" AS "creator_username", 
    "creator"."email" AS "creator_email", 
    "creator"."createdAt" AS "creator_createdAt", 
    "creator"."updatedAt" AS "creator_updatedAt"
FROM 
    "test" "test"
INNER JOIN (
    SELECT 
        "t"."creatorId" AS "creatorId", 
        MAX("t"."wpm") AS "max_wpm"
    FROM 
        "test" "t"
    WHERE 
        "t"."time" = $1
    GROUP BY 
        "t"."creatorId"
) "max_tests" 
    ON max_tests."max_wpm" = "test"."wpm" 
    AND max_tests."creatorId" = "test"."creatorId"
LEFT JOIN 
    "user" "creator" 
    ON "creator"."uid" = "test"."creatorId"
WHERE 
    "test"."time" = $1 
    AND "test"."accuracy" = (
        SELECT 
            MAX("t2"."accuracy") 
        FROM 
            "test" "t2"
        WHERE 
            "t2"."time" = $1 
            AND "t2"."wpm" = "test"."wpm"
            AND "t2"."creatorId" = "test"."creatorId"
    )
    AND "test"."createdAt" = (
        SELECT 
            MIN("t3"."createdAt")
        FROM 
            "test" "t3"
        WHERE 
            "t3"."creatorId" = "test"."creatorId"
            AND "t3"."wpm" = "test"."wpm"
            AND "t3"."accuracy" = "test"."accuracy"
    )
ORDER BY 
    "test"."wpm" DESC, 
    "test"."accuracy" DESC, 
    "test"."createdAt" ASC;

This is the leaderboard resolver:

@Query(() => LeaderBoard)
  async leaderboard(
    @Ctx() ctx: Context,
    @Arg('uid') uid: string,
    @Arg('time') time: number
  ): Promise<LeaderBoard> {
    const qb = ctx.em
      .createQueryBuilder(Test, 'test')
      .innerJoin(
        (subQuery) =>
          subQuery
            .select('MAX(t.wpm)', 'max_wpm')
            .addSelect('t.creatorId', 'creatorId')
            .from(Test, 't')
            .where('t.time = :time', { time: time })
            .groupBy('t.creatorId'),
        'max_tests',
        'max_tests.max_wpm = test.wpm AND max_tests."creatorId" = test."creatorId"'
      )
      .where('test.time = :time', { time: time })
      .andWhere((qb) => {
        const subQuery = qb
          .subQuery()
          .select('MAX(t2.accuracy)')
          .from(Test, 't2')
          .where('t2.time = :time')
          .andWhere('t2.wpm = test.wpm')
          .andWhere('t2.creatorId = test.creatorId')
          .setParameter('time', time)
          .getQuery();
        return `test.accuracy = (${subQuery})`;
      })
      .andWhere((qb) => {
        const subQuery = qb
          .subQuery()
          .select('MIN(t3.createdAt)')
          .from(Test, 't3')
          .where('t3.time = :time')
          .where('t3.creatorId = test.creatorId')
          .andWhere('t3.wpm = test.wpm')
          .andWhere('t3.accuracy = test.accuracy')
          .setParameter('time', time)
          .getQuery();
        return `test."createdAt" = (${subQuery})`;
      })
      .leftJoinAndSelect('test.creator', 'creator')
      .orderBy('test.wpm', 'DESC')
      .addOrderBy('test.accuracy', 'DESC')
      .addOrderBy('test.createdAt', 'ASC');

    const tests = await qb.getMany();
    let userRank: number = -1;
    let userName: string = '';
    let userWpm: number = 0;
    let userAccuracy: number = 0;
    let userRawWpm: number = 0;
    let userTime: number = 0;
    let testTaken: string = '';

    let leaderBoard = [];

    let prevWpm = Infinity;
    let prevAccuracy = 0;
    let rank = 0;

    for (let i = 0; i < tests.length; i++) {
      const { wpm, accuracy } = tests[i];

      if (wpm < prevWpm || (wpm === prevWpm && accuracy > prevAccuracy)) {
        rank = i + 1;
        prevWpm = wpm;
        prevAccuracy = accuracy;
      } else {
        rank += 1;
      }

      leaderBoard.push({
        rank,
        user: tests[i].creator.username,
        wpm: wpm,
        rawWpm: tests[i].rawWpm,
        time: tests[i].time,
        accuracy: accuracy,
        testTaken: tests[i].testTaken,
      });

      if (tests[i].creatorId === uid) {
        userRank = rank;
        userName = tests[i].creator.username;
        userWpm = tests[i].wpm;
        userRawWpm = tests[i].rawWpm;
        userTime = tests[i].time;
        userAccuracy = tests[i].accuracy;
        testTaken = tests[i].testTaken;
      }
    }
    // change slice end no to whatever number of tests have to be shown
    leaderBoard = leaderBoard.slice(0, 50);

    return {
      leaderBoard,
      user: {
        rank: userRank,
        user: userName,
        wpm: userWpm,
        rawWpm: userRawWpm,
        time: userTime,
        accuracy: userAccuracy,
        testTaken: testTaken,
      },
    };
  }

This is the test entity:

@ObjectType()
@Entity()
export class Test extends BaseEntity {
  @Field()
  @PrimaryGeneratedColumn()
  id!: number;

  @Field()
  @Column()
  creatorId: string;

  @ManyToOne(() => User, (user) => user.tests)
  @JoinColumn({ name: 'creatorId', referencedColumnName: 'uid' })
  creator: User;

  @Field()
  @Column()
  time: number;

  @Field(() => Float)
  @Column('double precision')
  accuracy: number;

  @Field()
  @Column()
  wpm: number;

  @Field()
  @Column()
  rawWpm: number;

  @Field()
  @Column()
  chars: string;

  @Field(() => String)
  @CreateDateColumn()
  createdAt: Date;

  @Field()
  @Column()
  testTaken: string;
}

The problem occurs when I run the query with my full dataset. Specifically, the query only returns one record in the leaderboard for time = 60, even though there are multiple records for different users. Interestingly, when I deleted all other records (keeping only the tests with time = 60), the query worked correctly and returned the expected results.

This leads me to believe the issue is related to how the data interacts with the query. My goal is to fetch only the highest WPM test for each user, with ties broken by accuracy and createdAt.

Why would the query work with a smaller dataset (with only tests for time = 60) but fail when other records (for example tests with time = 15,45,30,120) are present? Is there something wrong with how I’m using JOIN or ORDER BY that’s causing this behavior?

You can access the site
I have opened an issue so incase you have a solution you can open a PR here: Github