--ちょっと手順通りにやってみるか...
--効率よいやり方は後で考える
--全体の平均値を出す
WITH avg_table AS
(
 SELECT
  AVG(POINT)
 FROM
  TEST_RESULTS
 WHERE
  TEST_ID = '100'
),

--各々の平均値の差を求める
avg_diff AS 
(
 SELECT
  USER_ID,
  POWER(POINT - (SELECT * FROM avg_table),2) AS avg_diff
 FROM
  TEST_RESULTS
 WHERE
  TEST_ID = '100'
 GROUP BY
  USER_ID
),

--標準偏差を求める
std_table AS 
(
 SELECT
  SQRT((SELECT SUM(avg_diff) FROM avg_diff)/(SELECT COUNT(USER_ID) FROM TEST_RESULTS WHERE TEST_ID = '100'))
 FROM
  TEST_RESULTS
 WHERE
  TEST_ID = '100'
)
--標準偏差ゲットしたので偏差値求めるよー

SELECT
 USER_ID AS USER,
 POINT AS PT,
 CASE
  WHEN (SELECT * FROM std_table) = 0 THEN 50
  ELSE ROUND((10 * (POINT - (SELECT * FROM avg_table))/(SELECT * FROM std_table)) + 50,1) 
 END AS DEV_VAL
FROM
 TEST_RESULTS
WHERE
 TEST_ID = '100'
GROUP BY
 USER_ID,POINT
ORDER BY
 DEV_VAL DESC,USER_ID