with base as (
  select
    *
  from
    TEST_RESULTS
  where
    TEST_ID = '100'
)
,heikin as (
  select
    avg(POINT) as 平均値
  from
    base
)
, diff as (
  select
    *
   ,base.POINT - heikin.平均値 as 個々の点数と平均値の差
  from
    base
  cross join
    heikin
)
, bunsan_0 as (
  select
    *
   ,POW(個々の点数と平均値の差, 2) as 個々の点数と平均値の差の二乗
  from
    diff
)
, bunsan as (
  select
    avg(個々の点数と平均値の差の二乗) as 分散
  from
    bunsan_0
)
, std_hensa as (
  select
    SQRT(分散) as 標準偏差
  from
    bunsan
)
, x10 as (
  select
    *
   ,((個々の点数と平均値の差 * 10) / 標準偏差) + 50 as 算出した偏差値
  from
    diff
  cross join
    std_hensa
)
select
  USER_ID as USER
 ,POINT as PT
 ,round(算出した偏差値, 1) as DEV_VAL
from
  x10
order by
  算出した偏差値 desc
 ,USER_ID asc