with average as (
select
  test_id as test_id
  , avg(point) as avg_p
from test_results
where test_id = '100'
group by test_id
), stdev as (
select
  t1.test_id as test_id
  , sqrt(sum(power(t1.point - t2.avg_p, 2)) / count(t1.user_id)) as stdev
from
  test_results t1
  left outer join average t2
  on t1.test_id = t2.test_id
where
  t1.test_id = '100'
group by t1.test_id
)
select
  t1.user_id as USER_ID
  , t1.point as POINT
  , round((((point - t2.avg_p) * 10.0) / t3.stdev) + 50.0, 1) as DEV_VAL
from test_results t1
inner join average t2 on t1.test_id = t2.test_id
inner join stdev t3 on t1.test_id = t3.test_id
where t1.test_id = '100'
order by DEV_VAL desc, USER_ID asc
;