WITH basedata AS (
	SELECT
		USER_ID
		,POINT
	FROM TEST_RESULTS
	WHERE TEST_ID = '100'
)
,heikinsamade AS (
	SELECT
		t1.USER_ID
		,t1.POINT
		,t2.avgpt
		,ROUND(t1.POINT - t2.avgpt) AS heikinsa
	FROM basedata AS t1
	CROSS JOIN
		(SELECT
			ROUND(AVG(POINT)) AS avgpt
		FROM basedata
		) AS t2
)
,jijo AS (
	SELECT
		USER_ID
		,ROUND(heikinsa * heikinsa) AS jijo
	FROM heikinsamade
)
,bunsanavg AS (
	SELECT
		ROUND(AVG(jijo)) AS bunavg
	FROM jijo
)
,hyoujunhensa AS (
	SELECT ROUND(SQRT(bunavg)) AS hensa
	FROM bunsanavg
)
SELECT
	t1.USER_ID AS USER
	,t1.POINT AS PT
	,ROUND((t1.heikinsa * 10)/t2.hensa + 50,1) AS DEV_VAL
FROM heikinsamade AS t1
CROSS JOIN hyoujunhensa AS t2
ORDER BY DEV_VAL DESC
	,USER ASC