-- サブクエリで平均点と標準偏差を計算
WITH Stats AS (
    SELECT
        AVG(POINT) AS avg_point,
        SQRT(SUM(POWER(POINT - avg_point, 2)) / COUNT(*)) AS stddev
    FROM
        TEST_RESULTS,
        (SELECT AVG(POINT) AS avg_point FROM TEST_RESULTS WHERE TEST_ID = '100') AS subquery
    WHERE
        TEST_ID = '100'
),
-- 偏差値を計算
Deviations AS (
    SELECT
        USER_ID AS USER,
        POINT AS PT,
        CASE
            WHEN s.stddev = 0 THEN 50
            ELSE ROUND((POINT - s.avg_point) * 10 / s.stddev + 50, 1)
        END AS DEV_VAL
    FROM
        TEST_RESULTS t
    JOIN
        Stats s ON t.TEST_ID = '100'
    WHERE
        t.TEST_ID = '100'
)
-- 結果を表示
SELECT
    USER,
    PT,
    DEV_VAL
FROM
    Deviations
ORDER BY
    DEV_VAL DESC,
    USER ASC;