with mean as (
    select
        avg(POINT) as MEAN,
        count(*) as N
    from
        TEST_RESULTS
    where
        TEST_ID == "100"
    group by
        TEST_ID
),
stdev as (
    select
        sqrt(
            sum(
                (TEST_RESULTS.POINT - mean.MEAN) *(TEST_RESULTS.POINT - mean.MEAN) / mean.N
            )
        ) as STDEV,
        mean.MEAN as MEAN
    from
        TEST_RESULTS,
        mean
    where
        TEST_RESULTS.TEST_ID == "100"
    group by
        TEST_RESULTS.TEST_ID
)
select
    TEST_RESULTS.USER_ID as USER,
    TEST_RESULTS.POINT as PT,
    round(
        case when stdev.STDEV<> 0 then (TEST_RESULTS.POINT - stdev.MEAN) * 10 / stdev.STDEV else 0 end + 50,
        1
    ) as DEV_VAL
from
    stdev,
    TEST_RESULTS
where
    TEST_RESULTS.TEST_ID == "100"
order by
    DEV_VAL desc,
    TEST_RESULTS.USER_ID