with tmp as (
--1.平均値
select 
    avg(point) as 平均値 
from test_results 
where test_id='100' 
),tmp1 as (
--2.個々の点数と平均値の差
select 
    test_results.user_id,
    tmp.平均値 as 平均値,
    point-tmp.平均値 as 個々の平均値の差 
from test_results 
join tmp 
where test_id='100' 
),tmp2 as (
--3.分散
select
    tmp1.平均値 as 平均値,
    avg(power(個々の平均値の差,2)) as 分散
from tmp1 
),tmp3 as (
--4.標準偏差
select 
    tmp2.平均値 as 平均値,
    sqrt(abs(分散)) as 標準偏差 
from tmp2 
),tmp4 as (
--5.6個々の点数と平均値との差に10かけた値を標準偏差で割る
select 
    test_results.user_id as USER,
    (point-tmp3.平均値)*10/tmp3.標準偏差 as 偏差値の元
from test_results 
join tmp3 
where test_id='100'
),tmp5 as (
--7.偏差値
select 
    tmp4.user,
    case 
        when round(tmp4.偏差値の元+50,1)=0 then 50 
        else round(tmp4.偏差値の元+50,1)
    end as 偏差値
from tmp4
)
select 
    tr.user_id as USER,
    tr.point as PT,
    tmp5.偏差値 as DEV_VAL 
from  test_results as tr 
inner join tmp5 
on tr.user_id=tmp5.user 
where test_id='100' 
order by DEV_VAL desc,user_id;