--【問題3】中央値の算出
WITH BASE AS(
SELECT
	USER_ID AS USER
,	POINT AS PT
FROM
	TEST_RESULTS
WHERE
	TEST_ID = '100'
),
HEIKIN AS(
SELECT
	AVG(PT) AS HEIKIN
FROM
	BASE
),
BUNSAN AS(
SELECT
	AVG((PT - HEIKIN) * (PT - HEIKIN)) AS BUNSAN
FROM
	BASE
,	HEIKIN
)
SELECT
	USER
,	PT
,	CASE WHEN SQRT(BUNSAN) = 0
		THEN 50
		ELSE ROUND((PT- HEIKIN) * 10 / SQRT(BUNSAN) + 50 ,1) 
		END AS DEV_VAL
FROM
	BASE
,	HEIKIN
,	BUNSAN
ORDER BY
	DEV_VAL DESC
,	USER