with tmp as (
--点数の平均値の算出
select
    user_id
    ,point
    ,test_id
    ,avg(point) over(partition by test_id)avg_point
from
    test_results
where
    test_id = '100'
), tmp2 as (
--個々の点数と平均値の差の算出
--分散(個々の点数と平均値の差の二乗の平均)の算出
--標準偏差（分散の正の平方根）の算出
select
    user_id
    ,point
    ,point - avg_point diff_point
    ,avg((point - avg_point)*(point - avg_point)) over(partition by test_id) var_point
    ,sqrt(avg((point - avg_point)*(point - avg_point)) over(partition by test_id)) st_point
from
    tmp
)
select
--個々の点数と平均との差に10を掛ける
--個々の点数と平均との差に10を掛けた値を標準偏差で割る
--標準偏差で割った値に50を足して偏差値を算出
    user_id "USER"
    ,point "PT"
    ,case
        when st_point = 0 then 50
        else round((diff_point * 10) / st_point + 50, 1) 
     end as "DEV_VAL"
from
    tmp2
order by
    "DEV_VAL" desc, "USER"
;