## Wednesday, August 20, 2008

### Calculate Total Possible Score for Wii Bowling, Power Throws

Needless to say I have too much time on my hands. I've become obsessed with two Wii games, Bowling and Tanks. I play over and over. I had a perfect game in bowling the other day which wasn't quite as exciting as I thought it would be.

I've also been playing the "Training" game of Power Throws (bowling). You are given 10 shots with another row added each frame. You start out with 10, then 15, then 21, etc. If you get all the pins, you get a bonus (total pins * 2). So far, my highest score has been 651.

I've never bothered to figure out what the total possible score is though.

SQL to the rescue.

Using analytics and the CONNECT BY LEVEL option (10g and higher), it's fairly easy:
`SELECT  pin_row,  pins,  running_total,  rt_with_bonusFROM  (    SELECT      rownum pin_row,      rownum pins,      SUM( rownum ) OVER ( ORDER BY rownum ) running_total,      ( SUM( rownum ) OVER ( ORDER BY rownum ) * 2 ) rt_with_bonus    FROM dual      CONNECT BY LEVEL <= 13  )WHERE pins >= 4`
I had to set the filter on the outer query because you start with 4 rows (10 pins). And the results:
`CJUSTICE@XE>BREAK ON REPORTCJUSTICE@XE>COMPUTE SUM OF RUNNING_TOTAL ON REPORCJUSTICE@XE>COMPUTE SUM OF RT_WITH_BONUS ON REPORCJUSTICE@XE>/   PIN_ROW       PINS RUNNING_TOTAL RT_WITH_BONUS---------- ---------- ------------- -------------         4          4            10            20         5          5            15            30         6          6            21            42         7          7            28            56         8          8            36            72         9          9            45            90        10         10            55           110        11         11            66           132        12         12            78           156        13         13            91           182                      ------------- -------------sum                             445           890`
Voila! 890 is the best possible score if I strike every frame. I did manage to pass my high score by 9 pins tonight as well. It's a great day!

So how come no one wants to hire a guy that can do this kind of fun stuff with SQL? ;)

Chen Shapira said...

Do it with MODEL!

Oh, and the ultimate solution for too much free time:
http://projecteuler.net/

I wasted about 3 month on this, and I know at least two Oracle aces who are even more obsessed than me...

oraclenerd said...

Well, since I have so much free time I might as well learn the MODEL stuff right?

I'll give it a run.

Nice site too. I'm going to give it a go but I think it's over my head (although I love(d) math, I didn't get too far in school)

Kris said...