gistfile1.txt

require 'data/jdb'

NB. Borrowed from Dan Bron's solution
'GROUPS POINTS' =. <"_1 |: ".;._2 noun define
 0  ;  0
 5  ;  3 2 1                NB.  5 <= #riders <= 10
 11 ;  7 5 4 3 2 1          NB. 11 <= #riders <= 20 
 21 ;  8 6 5 4 3 2 1        NB. 21 <= #riders <= 49
 50 ; 10 8 7 6 5 4 3 2 1    NB. 50 or more #riders 
)


vert  =: |:@:(<"_1&>)
freq=.({.,#)/.~             NB. Can also use jdb queries for this


NB. Gets the second column from the matching key
lookup=. 4 : 0
  keys=.0{"1 y
  keyidx=.keys I.@:=~ ] NB. keyidx=: 3 : 'y I.@:= keys'
  idx=.keyidx"0 x
  1{"1 idx { y  
)

NB. Returns x if x < # y otherwise, 0
safeIndex  	=. [ * (<: #) NB. 4 : 'x * x <: # y'

DBPATH=: jpath'~temp'

RacerCols=: 0 : 0
  rname varchar;
)


RaceCols=: 0 : 0
  rno int
  rname racer;
  place int
)

RacePoints=: 0 : 0
 rno race
 points int
)

build=: 3 : 0
  resetall_jdb_''
  f=. Open_jdb_ y
  Drop__f 'races'
  d=. Create__f 'races'
  Create__d 'racer';RacerCols
  Create__d 'race';RaceCols
  Create__d 'racepoints';RacePoints
  destroy__f''

)

opendb=: 3 : 0
  resetall_jdb_''
  f=. Open_jdb_ y
  locD=: Open__f 'races'
  i.0 0
)

build DBPATH

opendb DBPATH

NB. Tries to insert the racer if it doesn't exist
insertRacer=: 3 : 0
try. 
  Insert__locD 'racer';<<(<y)
catch.
end.
)

addResult=: 3 : 0
  rno=.0{::y
  rname=.1{::y
  place=.2{::y
  insertRacer rname
  Insert__locD 'race';<rno;rname;place
)

addResult 1;'Bob';1
addResult 1;'Joe';2


NB. Add 5  racers with 15 races so that we can get some scores back
addResult"1 each (<"1 vert (3 :'(_1]\1+i.y);(_1]\y # ''1'');_1]\y # 1') 15)
addResult"1 each (<"1 vert (3 :'(_1]\1+i.y);(_1]\y # ''2'');_1]\y # 2') 15)
addResult"1 each (<"1 vert (3 :'(_1]\1+i.y);(_1]\y # ''3'');_1]\y # 3') 15)
addResult"1 each (<"1 vert (3 :'(_1]\1+i.y);(_1]\y # ''4'');_1]\y # 4') 15)
addResult"1 each (<"1 vert (3 :'(_1]\1+i.y);(_1]\y # ''5'');_1]\y # 5') 15)


pointsForRace=: 3 : '> ({: I. y >:  >GROUPS) } POINTS'
scorePlace =.  4 : '(x safeIndex"1 >y) } (0,>y)'

rs=. Read__locD 'rno,rname,place from race'
tbl=. (vert 1}"1 rs)

                                         NB. Can also use jdb
racefreq=.freq > 0 {:: 1{"1 rs           NB.  Reads__locD 'racer_count:count rno by rno from race'

buildRow=: 3 : 0
  rno=.0{::y
  rname=. > (1{::y)
  place=:2{::y
  
  racers=:0{:: rno lookup racefreq
  score=.place scorePlace pointsForRace racers

  (<'insert';'racepoints';<(rno;rname);score)
)

rows=.buildRow"1 tbl
Alter__locD rows

points=. vert 1}"1 Read__locD 'sum points,count rno by rname from racepoints'
category =.  4 <. 1 + 35 30 20 I. (* 2&<:)  NB. Borrowed from Dan Bron's solution

categorize=: 3 : 0
  score=:1{::y
  races=.2{::y
  rname=. > (0{::y)
  rname;(score category races)
)

categorize"1 points

NB. ------------------

 categorize"1 points
┌───┬─┐
│Bob│4│
├───┼─┤
│Joe│4│
├───┼─┤
│1  │1│
├───┼─┤
│2  │2│
├───┼─┤
│3  │4│
├───┼─┤
│4  │4│
├───┼─┤
│5  │4│
├───┼─┤
│6  │4│
└───┴─┘


Reads__locD '** from racepoints where place=1'



┌────────┬──────────┬─────┬──────┐
│race.rno│race.rname│place│points│
├────────┼──────────┼─────┼──────┤
│ 1      │Bob       │1    │3     │
│ 1      │1         │1    │3     │
│ 2      │1         │1    │3     │
│ 3      │1         │1    │3     │
│ 4      │1         │1    │3     │
│ 5      │1         │1    │3     │
│ 6      │1         │1    │3     │
│ 7      │1         │1    │3     │
│ 8      │1         │1    │3     │
│ 9      │1         │1    │3     │
│10      │1         │1    │3     │
│11      │1         │1    │3     │
│12      │1         │1    │3     │
│13      │1         │1    │3     │
│14      │1         │1    │3     │
│15      │1         │1    │3     │
└────────┴──────────┴─────┴──────┘
   
 Reads__locD '** from race where race.rname=''Bob'''
┌───┬─────┬─────┐
│rno│rname│place│
├───┼─────┼─────┤
│1  │Bob  │1    │
└───┴─────┴─────┘

Reads__locD '** from race where race.rname=''1'''
┌───┬─────┬─────┐
│rno│rname│place│
├───┼─────┼─────┤
│ 1 │1    │1    │
│ 2 │1    │1    │
│ 3 │1    │1    │
│ 4 │1    │1    │
│ 5 │1    │1    │
│ 6 │1    │1    │
│ 7 │1    │1    │
│ 8 │1    │1    │
│ 9 │1    │1    │
│10 │1    │1    │
│11 │1    │1    │
│12 │1    │1    │
│13 │1    │1    │
│14 │1    │1    │
│15 │1    │1    │
└───┴─────┴─────┘
   
 Reads__locD 'total_points:sum points,race_count:count rno by rname from racepoints'
┌─────┬────────────┬──────────┐
│rname│total_points│race_count│
├─────┼────────────┼──────────┤
│Bob  │ 3          │ 1        │
│Joe  │ 2          │ 1        │
│1    │45          │15        │
│2    │30          │15        │
│3    │15          │15        │
│4    │ 0          │15        │
│5    │ 0          │15        │
│6    │ 0          │15        │
└─────┴────────────┴──────────┘