with avg_tbl as (
  SELECT
    AVG(POINT) OVER(PARTITION BY TEST_ID) AS AVG_PT
    -- -- 分散を求める
    -- SUM((POINT - AVG(POINT) OVER(PARTITION BY TEST_ID)) * (POINT - AVG(POINT) OVER(PARTITION BY TEST_ID))) OVER(PARTITION BY TEST_ID) / COUNT(*) OVER(PARTITION BY TEST_ID) AS VARIANCE_PT,
    -- -- 標準偏差を求める
    -- SQRT(SUM((POINT - AVG(POINT) OVER(PARTITION BY TEST_ID)) * (POINT - AVG(POINT) OVER(PARTITION BY TEST_ID))) OVER(PARTITION BY TEST_ID) / COUNT(*) OVER(PARTITION BY TEST_ID)) AS STDEV_PT
  FROM TEST_RESULTS
  WHERE TEST_ID = '100'
), bunsan as (
  SELECT
    -- 分散を求める
    SUM((POINT - AVG_PT) * (POINT - AVG_PT)) OVER(PARTITION BY TEST_ID) / COUNT(*) OVER(PARTITION BY TEST_ID) AS VARIANCE_PT
  FROM TEST_RESULTS, avg_tbl
  WHERE TEST_ID = '100'
)
SELECT
  distinct USER_ID AS USER,
  POINT AS PT,
  ROUND(
    CASE
      -- 標準偏差がゼロの場合は、偏差値を50とする
      WHEN SQRT(VARIANCE_PT) = 0 THEN 50
      -- 標準偏差がゼロでない場合は、偏差値を算出する
      ELSE (POINT - AVG_PT) * 10 / SQRT(VARIANCE_PT) + 50
    END,
    1 -- 小数点第2位を四捨五入
  ) AS DEV_VAL
FROM TEST_RESULTS, bunsan, avg_tbl
  WHERE TEST_ID = '100'
AND SQRT(VARIANCE_PT) IS NOT NULL -- 総数が1の場合、標準偏差がNULLになるため除外する
ORDER BY DEV_VAL DESC, USER_ID ASC;