-- SELECT * FROM test_results;
WITH SUB AS(
    SELECT 
        AVG(POINT) AS AVERAGE
    FROM 
        test_results
    WHERE
        test_id='100'
),
    SUB2 AS (
    SELECT 
        SQRT(
            AVG(
                (test_results.POINT - sub.average)*(test_results.POINT - sub.average)
            )
        ) AS standard_deviation
    FROM
        test_results
        CROSS JOIN sub
    WHERE
        test_id='100'
)
    
SELECT
    USER_ID AS USER,
    POINT AS PT,
    CASE
        WHEN standard_deviation <> 0
            THEN ROUND((point-average)*10/standard_deviation,1)+50
    ELSE
        50
    END AS DEV_VAL
FROM
    test_results
    CROSS JOIN sub
    CROSS JOIN sub2
ORDER BY
    DEV_VAL DESC,
    USER_ID