.sql

-- starting pieces is 100, 250, or 1000
-- randomizer_id has 3 values
-- perfect_clears could be 0-10 for 100 pieces, 0-100 for 1000 pieces
 SELECT r1.game_id, users.user_name, r1.perfect_clears, r1.time_taken,
        DATE(r1.timestamp) set_on, (replays.record IS NOT NULL) as has_replay
 FROM pcb_records r1
 LEFT OUTER JOIN pcb_records r2
     ON (? OR r2.timestamp >= NOW() - INTERVAL ? DAY)
    AND r2.user_id = r1.user_id
    AND r2.starting_pieces = r1.starting_pieces
    AND r2.randomizer_id = r1.randomizer_id
    AND r2.is_highscore_eligible = 1
    AND (r2.perfect_clears > r1.perfect_clears
        OR (r2.perfect_clears = r1.perfect_clears
            AND r2.time_taken < r1.time_taken))
 JOIN users ON r1.user_id = users.user_id
 JOIN randomizers ON r1.randomizer_id = randomizers.randomizer_id
 LEFT JOIN replays ON r1.game_id = replays.game_id
 WHERE r2.user_id IS NULL
   AND r1.starting_pieces = ?
   AND r1.randomizer_id = ?
   AND r1.is_highscore_eligible = 1
   AND (? OR r1.timestamp >= NOW() - INTERVAL ? DAY)
 ORDER BY r1.perfect_clears DESC, r1.time_taken ASC
 LIMIT 10