-- SELECT STUDENT AS [学生],
--     SCORE AS [得点],
--     AVERAGE AS [平均点],
--     STANDARD_DEVIATION AS [標準偏差],
--     -- 得点と平均点との差に10を掛けて標準偏差で割り50を足し偏差値を求める
--     (((SCORE - AVERAGE) * 10) / STANDARD_DEVIATION) + 50 AS [偏差値]
WITH TEST_RESULTS2 AS (
    SELECT USER_ID,
        POINT
    FROM TEST_RESULTS
    WHERE TEST_ID = '100'
),
STDEV AS (
    SELECT SQRT(
            SUM(
                POWER(
                    POINT - (
                        SELECT AVG(POINT)
                        FROM TEST_RESULTS2
                    ),
                    2
                )
            ) / COUNT(*)
        ) AS std_dev
    FROM TEST_RESULTS2
)
SELECT USER_ID USER,
    POINT PT,
    ROUND(
        (((POINT - AVERAGE) * 10) / STANDARD_DEVIATION) + 50,
        1
    ) AS DEV_VAL
FROM (
        SELECT USER_ID,
            POINT,
            -- AVG関数で平均点を求める
            (
                SELECT AVG(POINT)
                FROM TEST_RESULTS2
            ) AS AVERAGE,
            -- STDEVP関数で標準偏差を求める
            (
                SELECT std_dev
                FROM STDEV
            ) AS STANDARD_DEVIATION
        FROM TEST_RESULTS2
    ) AS TEST_RESULTS2
ORDER BY DEV_VAL DESC,
    USER_ID;