WITH tbl_avg AS (
    SELECT
        TEST_ID
      , cast(AVG(POINT) as REAL) AS avg_pt
    FROM
        TEST_RESULTS
    GROUP BY
        TEST_ID
)
SELECT
      USER_ID AS USER
    , POINT AS PT
    , CASE
        WHEN hyoujunn = 0 THEN 50
        ELSE ROUND(50 + (POINT - avg_pt) * 10 / hyoujunn, 1)
      END AS DEV_VAL
FROM (
    SELECT
        *
      , POW(AVG(POW(TR.POINT - tbl_avg.avg_pt, 2)) OVER(), 0.5) AS hyoujunn -- 標準偏差
    FROM TEST_RESULTS AS TR
    INNER JOIN tbl_avg
        ON TR.TEST_ID = tbl_avg.TEST_ID
    WHERE
        TR.TEST_ID = '100'
     ) AS TR2
WHERE
    TR2.TEST_ID = '100'
ORDER BY
    DEV_VAL DESC
  , USER ASC