How to compare records by properties?

Question

Database design

Users take a test consisting of 40 questions with NO CORRECT answer, just the answer options are saved. Questions with one answer. The number of users who passed the test (number of results) can be tens of thousands. Each new user passes the test, and the result is entered into the table (that is, new ones appear over time).
The user has added his own result, which needs to be compared with other results and post a table sorted from best to worst (most relevant). What is the best way to solve this problem in terms of performance?
Important! Each new user with their own result is a standard of correct answers in relation to others.
Option 1 (unsuccessful). Create a table with 40 properties. Let's fill it with 10000 records (for example). Next, select one of the records and compare it with the rest. Those. there will be a filter on records (record fields). In this option, only exactly matched records will be selected and there will be no way to build a table with sorting from best to worst.
Option 2 (unsuccessful). Through the loop, open each record and compare the properties, creating a new array to build the table. But there are a lot of records and the server will be bent.

Answer 1

There is 1 trick with which you can implement your task, sort by Levenshtein distance. I will give you an example in php. I think you will find how to implement it on the sql side:

$me = '1111111111';
$results = [
    '2222222222',
    '1111211141',
    '1111111111',
    '1111112111',
    '1121111111'
];

// ASC
usort($results, function ($a, $b) use ($me) {
    return levenshtein($me, $a) > levenshtein($me, $b);
});
var_dump('ACS', $results);

// DESC
usort($results, function ($a, $b) use ($me) {
    return levenshtein($me, $a) < levenshtein($me, $b);
});
var_dump('DESC', $results);

PS The algorithm itself in this case is not quite suitable, but you can make a similar one.

Answer 2

It was necessary to immediately clarify that there are no answer options.
It's redundant. I see this structure.
Table with questions: id, question text
, Answer table: id, question id, user id, answer, reference flag
Table with statistics: id, user id, score (% of reference answers)
The 1st table will remain unchanged (until new questions are added )
in the 2nd, new answers will be added, and with the new standard, the flags will change.
in the 3rd there will be a recalculation after updating the standard, based on it, users can be sorted by account