ソースコード

select
USER_ID as USER
,POINT as PT
,case when sorta=0 then  round((POINT -  (select avg(POINT)  from TEST_RESULTS where TEST_ID = '100'))*10/50+ 50,1)
      else round((POINT -  (select avg(POINT)  from TEST_RESULTS where TEST_ID = '100'))*10/sorta+ 50,1)
 end as DEV_VAL
--,round((POINT -  (select avg(POINT)  from TEST_RESULTS where TEST_ID = '100'))*10/sorta+ 50,1) as DEV_VAL
from TEST_RESULTS,
(
 select 
--  avg((POINT -  (select avg(POINT)  from TEST_RESULTS where TEST_ID = '100') ) * (POINT -  (select avg(POINT)  from TEST_RESULTS where TEST_ID = '100'))) avga,
 sqrt(avg((POINT -  (select avg(POINT)  from TEST_RESULTS where TEST_ID = '100') ) * (POINT -  (select avg(POINT)  from TEST_RESULTS where TEST_ID = '100')))) as sorta
from TEST_RESULTS
where TEST_ID = '100'
)
where TEST_ID = '100'
order by 
round((POINT -  (select avg(POINT)  from TEST_RESULTS where TEST_ID = '100'))*10/sorta+ 50,1) desc,
USER_ID
提出情報
提出日時2023/03/14 22:42:14
コンテスト第5回 SQLコンテスト
問題偏差値の算出
受験者20230217mori
状態 (詳細)AC
(Accepted: 正答)
メモリ使用量75 MB
メッセージ
テストケース(通過数／総数)
4/4
状態
メモリ使用量
データパターン1
AC
75 MB
データパターン2
AC
75 MB
データパターン3
AC
75 MB
データパターン4
AC
75 MB