WITH basedata AS (
	SELECT
		USER_ID
		,POINT
	FROM TEST_RESULTS
	WHERE TEST_ID = '100'
)
,heikinscore AS (
	SELECT AVG(POINT) AS avgpt
	FROM basedata
)
,jijo AS (
	SELECT
		USER_ID
		,(POINT - avgpt)*(POINT - avgpt) AS jijo
	FROM basedata
	CROSS JOIN heikinscore
)
,DEVIATION AS (
	SELECT SQRT(bunavg) AS stdev
	FROM(
		SELECT AVG(jijo) AS bunavg
		FROM jijo
	)
)
SELECT
	t1.USER_ID AS USER
	,t1.POINT AS PT
	,CASE WHEN t2.stdev <> 0 THEN
		ROUND(((t1.POINT - t3.avgpt) * 10)/t2.stdev,1) + 50
	END AS DEV_VAL
FROM TEST_RESULTS AS t1
CROSS JOIN DEVIATION AS t2
CROSS JOIN heikinscore AS t3
WHERE t1.TEST_ID = '100'
ORDER BY DEV_VAL DESC ,USER ASC