WITH avg_point AS (
    SELECT AVG(POINT) AS average_point
    FROM TEST_RESULTS
    WHERE TEST_ID = '100'
), deviation AS (
    SELECT USER_ID, POINT, (POINT - avg_point.average_point) AS deviation
    FROM TEST_RESULTS, avg_point
    WHERE TEST_ID = '100'
), variance AS (
    SELECT AVG(deviation * deviation) AS variance
    FROM deviation
), standard_deviation AS (
    SELECT SQRT(variance.variance) AS stdev
    FROM variance
), deviation_values AS (
    SELECT USER_ID, POINT, ROUND((10.0 * deviation / stdev) + 50.0, 1) AS dev_val
    FROM deviation, standard_deviation
    WHERE stdev > 0
)
SELECT USER_ID AS "USER", POINT AS PT, IFNULL(dev_val, 50) AS DEV_VAL
FROM deviation_values
ORDER BY dev_val DESC, USER ASC;