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
    case when SQRT(分散)=0 then 50.0 else SQRT(分散) end 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
  DEV_VAL desc
 ,USER_ID asc