with AVG_OF_TEST_ID_100 as(
    select
    	avg(POINT) as avg
    from
    	TEST_RESULTS
    where
    	TEST_ID = '100'
)
, POINT_SUB_AVG as(
    select
        USER_ID
        , (POINT - avg) as 'SUB'
    from
        TEST_RESULTS, AVG_OF_TEST_ID_100
    where
        TEST_ID = '100'
)
, STANDARD_DEVIATION as(
    select
        abs(POW(avg(SUB*SUB), 0.5)) as 'STANDARD_DEVIATION'
    from
        POINT_SUB_AVG
)
select
    t.USER_ID as 'USER'
    , t.POINT as 'PT'
    , CASE
        WHEN STANDARD_DEVIATION = 0 THEN 50
        ELSE round((SUB*10 / STANDARD_DEVIATION) + 50, 1)
    END as 'DEV_VAL'
from
    TEST_RESULTS t
    , STANDARD_DEVIATION s
    inner join POINT_SUB_AVG p
        on t.USER_ID = p.USER_ID
where
    TEST_ID = '100'
order by
    DEV_VAL desc
    , USER asc
;