with
    avrg as(
        select
            avg(point) as avg_score
        from
            test_results
        where test_id = '100'
        )
    ,stdev as (
       select
            sqrt(avg(power(point-avg_score,2))) as stdev
        from
            test_results cross join avrg
        where test_id = '100'
            )

select
    user_id as USER
    ,point as PT
    ,case
        when stdev !=0 then round(((point-avg_score)*10/stdev)+50,1)
        else 50
    end as DEV_VAL
    
from
    test_results
    cross join stdev
    cross join avrg
where
    test_id = '100'
order by
    DEV_VAL DESC
    ,USER_ID ASC

;