WITH tbl AS (
    SELECT USER_ID, POINT, TEST_ID
        , AVG(POINT) OVER (PARTITION BY TEST_ID) AS aver
        , POINT - AVG(POINT) OVER (PARTITION BY TEST_ID) AS diff
        -- , SQRT(
        --     AVG(POW(POINT - AVG(POINT) OVER (PARTITION BY TEST_ID), 2)) OVER (PARTITION BY TEST_ID)
        --     ) AS sigma
        
    FROM TEST_RESULTS
    WHERE TEST_ID = '100'
), tbl2 AS (
    SELECT USER_ID, POINT
        , aver, diff
        , SQRT(
            AVG(POW(diff, 2)) OVER (PARTITION BY TEST_ID)
            ) AS sigma
    FROM tbl
)

-- SELECT *
-- FROM tbl2

SELECT
    USER_ID AS USER
    , POINT AS PT
    , ROUND((diff * 10.0) / sigma + 50 , 1)
    
    AS DEV_VAL
    
FROM tbl2
ORDER BY 2 DESC, 1