WITH TMP AS(
SELECT 
    USER_ID
    ,POINT
    ,AVG(POINT) OVER (PARTITION BY TEST_ID) AS KEI
    ,(POINT - AVG(POINT) OVER (PARTITION BY TEST_ID)) * (POINT - AVG(POINT) OVER (PARTITION BY TEST_ID)) AS A
FROM TEST_RESULTS
WHERE TEST_ID = '100' AND POINT IS NOT NULL
)
,COUNT_USER AS(
    SELECT
        COUNT(USER_ID) AS COUNT
    FROM TEST_RESULTS
    WHERE TEST_ID = '100' AND POINT IS NOT NULL
)
SELECT
    USER_ID AS USER
    ,POINT AS PT
    ,CASE
        WHEN SUM(A)OVER() = 0 THEN 50.0
        ELSE ROUND((POINT-KEI)/SQRT(SUM(A)OVER()/COUNT)*10+50, 1)
    END AS DEV_VAL
FROM TMP CROSS JOIN COUNT_USER
ORDER BY 3 DESC,1