WITH avg_point AS (
SELECT AVG(POINT) AS AVG_PT
FROM TEST_RESULTS
WHERE TEST_ID = '100'
), deviation AS (
SELECT USER_ID, POINT, AVG_PT,
ROUND(((POINT - AVG_PT) * 10 / SQRT((SELECT COUNT() FROM TEST_RESULTS WHERE TEST_ID = '100'))), 1) AS DEV_VAL
FROM TEST_RESULTS, avg_point
WHERE TEST_RESULTS.TEST_ID = '100'
), final_result AS (
SELECT USER_ID AS USER, POINT AS PT,
CASE WHEN (SELECT COUNT() FROM deviation WHERE DEV_VAL = 0) > 0 THEN 50
ELSE ROUND((DEV_VAL / (SELECT SQRT(AVG(POWER(POINT - AVG_PT, 2))) FROM deviation)) * 10 + 50, 1)
END AS DEV_VAL
FROM deviation
)
SELECT USER, PT, DEV_VAL
FROM final_result
ORDER BY DEV_VAL DESC, USER ASC;