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