How to calculate bowling scores using SQL. No Store Procedures allowed!¶
The scoring rules for American Bowling are simple on paper, but can be a chore to calculate by hand. This is a fun exercise to calculate the scores in mass using SQL. Because if you can't do it in SQL, you're life is too easy!
If you don't know the rules, here's a quick summary: https://www.kidslearntobowl.com/how-to-keep-score/
Data Preparation¶
Making a table to store the raw data for the individual games.
We're not going into full data modelling by adding players and timestamps. We're keeping it simple for this example.
DROP TABLE IF EXISTS game_rolls;
CREATE TABLE game_rolls (
game int,
frame int,
roll int,
pins int
);
Add a Perfect Game¶
A perfect game is 12 strikes in a row. 10 strikes in the first 9 frames and 2 strikes in the 10th frame.
-- Perfect Game
-- Total: 300
INSERT INTO game_rolls (game, frame, roll, pins)
VALUES
(1, 1, 1, 10),
(1, 2, 1, 10),
(1, 3, 1, 10),
(1, 4, 1, 10),
(1, 5, 1, 10),
(1, 6, 1, 10),
(1, 7, 1, 10),
(1, 8, 1, 10),
(1, 9, 1, 10),
(1, 10, 1, 10),
(1, 10, 2, 10),
(1, 10, 3, 10);
Add an Unlucky Second Game¶
We managed to knock down 1 pin each frame and missed the second roll. Repeat this for all 10 frames.
-- 1 pin each frame.
-- Total: 10
INSERT INTO game_rolls (game, frame, roll, pins)
VALUES
(2, 1, 1, 1),
(2, 1, 2, 0),
(2, 2, 1, 1),
(2, 2, 2, 0),
(2, 3, 1, 1),
(2, 3, 2, 0),
(2, 4, 1, 1),
(2, 4, 2, 0),
(2, 5, 1, 1),
(2, 5, 2, 0),
(2, 6, 1, 1),
(2, 6, 2, 0),
(2, 7, 1, 1),
(2, 7, 2, 0),
(2, 8, 1, 1),
(2, 8, 2, 0),
(2, 9, 1, 1),
(2, 9, 2, 0),
(2, 10, 1, 1),
(2, 10, 2, 0);
Add Gutter Ball and Spare¶
Now, we're showing off our skills. Throw the first ball into the gutter on purpose and take down the rest in the second roll for a spare.
-- 0 then 10 each frame
-- Total: 100
INSERT INTO game_rolls (game, frame, roll, pins)
VALUES
(3, 1, 1, 0),
(3, 1, 2, 10),
(3, 2, 1, 0),
(3, 2, 2, 10),
(3, 3, 1, 0),
(3, 3, 2, 10),
(3, 4, 1, 0),
(3, 4, 2, 10),
(3, 5, 1, 0),
(3, 5, 2, 10),
(3, 6, 1, 0),
(3, 6, 2, 10),
(3, 7, 1, 0),
(3, 7, 2, 10),
(3, 8, 1, 0),
(3, 8, 2, 10),
(3, 9, 1, 0),
(3, 9, 2, 10),
(3, 10, 1, 0),
(3, 10, 2, 10);
Add Realistic Game with Strikes and Spares¶
This is my typical night out, never quite getting halfway to perfection (300). If I spent more time bowling and less time writing SQL maybe I'd get better!
INSERT INTO game_rolls (game, frame, roll, pins)
VALUES
(4, 1, 1, 7), -- First frame: 7 pins
(4, 1, 2, 2), -- Spare with 2 pins => = 9
(4, 2, 1, 10), -- Second frame: Strike => 9+10+5+4=28
(4, 3, 1, 5), -- Third frame: 5 pins
(4, 3, 2, 4), -- Open frame with 4 pins => 28+9=37
(4, 4, 1, 8), -- Fourth frame: 8 pins
(4, 4, 2, 1), -- Open frame with 1 pin => 37+9=46
(4, 5, 1, 10), -- Fifth frame: Strike => 46+10+6+3=65
(4, 6, 1, 6), -- Sixth frame: 6 pins
(4, 6, 2, 3), -- Open frame with 3 pins => 65+9=74
(4, 7, 1, 9), -- Seventh frame: 9 pins
(4, 7, 2, 0), -- Open frame with 0 pins => 74+9=83
(4, 8, 1, 10), -- Eighth frame: Strike => 83+10+9=102
(4, 9, 1, 7), -- Ninth frame: 7 pins
(4, 9, 2, 2), -- Open frame with 2 pins => 102+9=111
(4, 10, 1, 10), -- Tenth frame: Strike
(4, 10, 2, 10), -- Bonus roll: Strike
(4, 10, 3, 8); -- Bonus roll: 8 pins => 111+10+10+8=139
Query to Calculate Scores¶
There are many ways to calculate the scores. We're going to make use of Window Functions to look ahead for bonus pins and look behind for spares.
SELECT
*,
COALESCE(LEAD(pins, 1) OVER (w), 0) AS next_pins,
COALESCE(LEAD(pins, 2) OVER (w), 0) AS next_next_pins,
roll = 1 AND pins = 10 AS is_strike,
roll = 2 AND (pins + LAG(pins, 1) OVER (w) = 10) AS is_spare
FROM game_rolls
WINDOW w AS (PARTITION BY game ORDER BY game, frame, roll)
;
Results:
| game | frame | roll | pins | next_pins | next_next_pins | is_strike | is_spare |
|---|---|---|---|---|---|---|---|
| 4 | 1 | 1 | 7 | 2 | 10 | false | false |
| 4 | 1 | 2 | 2 | 10 | 5 | false | false |
| 4 | 2 | 1 | 10 | 5 | 4 | true | false |
| 4 | 3 | 1 | 5 | 4 | 8 | false | false |
| 4 | 3 | 2 | 4 | 8 | 1 | false | false |
| 4 | 4 | 1 | 8 | 1 | 10 | false | false |
| 4 | 4 | 2 | 1 | 10 | 6 | false | false |
| 4 | 5 | 1 | 10 | 6 | 3 | true | false |
| 4 | 6 | 1 | 6 | 3 | 9 | false | false |
| 4 | 6 | 2 | 3 | 9 | 0 | false | false |
| 4 | 7 | 1 | 9 | 0 | 10 | false | false |
| 4 | 7 | 2 | 0 | 10 | 7 | false | false |
| 4 | 8 | 1 | 10 | 7 | 2 | true | false |
| 4 | 9 | 1 | 7 | 2 | 10 | false | false |
| 4 | 9 | 2 | 2 | 10 | 10 | false | false |
| 4 | 10 | 1 | 10 | 10 | 8 | true | false |
| 4 | 10 | 2 | 10 | 8 | 0 | false | false |
| 4 | 10 | 3 | 8 | 0 | 0 | false | false |
That looks right. The future pins are correct and the strikes and spares are correctly identified.
If we wanted, we could make a view, but we're going to continue with the query by using Common Table Expressions (CTE).
We'll call the first CTE stats and build on it with the next CTE to calculate the bonus based on the rules of
strikes and spares. Strikes get the next two rolls and spares get the next roll.
There is a special condition for the last frame. We accept the pins as final since there are no future frames to look at. We don't have to 'wait' to score the last frame. We can score it immediately.
I have to admit, this was the hardest part to figure out and I was stuck on it for longer than I'd like to admit. The perfect game scenario was totalling 320 for the longest time! (Perfect games of 12 strikes in a row are 300 points, not 320)
WITH stats AS (
SELECT
*,
COALESCE(LEAD(pins, 1) OVER (w), 0) AS next_pins,
COALESCE(LEAD(pins, 2) OVER (w), 0) AS next_next_pins,
roll = 1 AND pins = 10 AS is_strike,
roll = 2 AND (pins + LAG(pins, 1) OVER (w) = 10) AS is_spare,
frame = (max(frame) OVER (PARTITION BY game)) AS is_last_frame
FROM game_rolls
WINDOW w AS (PARTITION BY game ORDER BY game, frame, roll)
)
SELECT
*,
CASE
WHEN is_last_frame THEN pins
WHEN is_strike THEN pins + next_pins + next_next_pins
WHEN is_spare THEN pins + next_pins
ELSE
pins
END
AS score
FROM stats
;
Results with pins per frame:
| game | frame | roll | pins | next_pins | next_next_pins | is_strike | is_spare | is_last_frame | score |
|---|---|---|---|---|---|---|---|---|---|
| 4 | 1 | 1 | 7 | 2 | 10 | false | false | false | 7 |
| 4 | 1 | 2 | 2 | 10 | 5 | false | false | false | 2 |
| 4 | 2 | 1 | 10 | 5 | 4 | true | false | false | 19 |
| 4 | 3 | 1 | 5 | 4 | 8 | false | false | false | 5 |
| 4 | 3 | 2 | 4 | 8 | 1 | false | false | false | 4 |
| 4 | 4 | 1 | 8 | 1 | 10 | false | false | false | 8 |
| 4 | 4 | 2 | 1 | 10 | 6 | false | false | false | 1 |
| 4 | 5 | 1 | 10 | 6 | 3 | true | false | false | 19 |
| 4 | 6 | 1 | 6 | 3 | 9 | false | false | false | 6 |
| 4 | 6 | 2 | 3 | 9 | 0 | false | false | false | 3 |
| 4 | 7 | 1 | 9 | 0 | 10 | false | false | false | 9 |
| 4 | 7 | 2 | 0 | 10 | 7 | false | false | false | 0 |
| 4 | 8 | 1 | 10 | 7 | 2 | true | false | false | 19 |
| 4 | 9 | 1 | 7 | 2 | 10 | false | false | false | 7 |
| 4 | 9 | 2 | 2 | 10 | 10 | false | false | false | 2 |
| 4 | 10 | 1 | 10 | 10 | 8 | true | false | true | 10 |
| 4 | 10 | 2 | 10 | 8 | 0 | false | false | true | 10 |
| 4 | 10 | 3 | 8 | 0 | 0 | false | false | true | 8 |
That looks good. The scores are correct and the last frame is scored correctly.
We could simply SUM everything at this point:
WITH stats AS (
SELECT
*,
COALESCE(LEAD(pins, 1) OVER (w), 0) AS next_pins,
COALESCE(LEAD(pins, 2) OVER (w), 0) AS next_next_pins,
roll = 1 AND pins = 10 AS is_strike,
roll = 2 AND (pins + LAG(pins, 1) OVER (w) = 10) AS is_spare,
frame = (max(frame) OVER (PARTITION BY game)) AS is_last_frame
FROM game_rolls
WINDOW w AS (PARTITION BY game ORDER BY game, frame, roll)
),
scores AS (
SELECT
*,
CASE
WHEN is_last_frame THEN pins
WHEN is_strike THEN pins + next_pins + next_next_pins
WHEN is_spare THEN pins + next_pins
ELSE
pins
END
AS score
FROM stats
)
SELECT
game,
SUM(score)
FROM scores
GROUP BY game
;
Results final SUM:
| game | sum |
|---|---|
| 1 | 300 |
| 2 | 10 |
| 3 | 100 |
| 4 | 139 |
Notice the use of CTEs again so we can build on the previous step. This is an important pattern that I follow strictly when building up queries. It allows me to extract parts into views in the future and provides a good place for meaningful names.
In Bowling, we normally to see a running score frame by frame. We can do this by adding a cumulative score to each frame with yet another Window Function.
WITH stats AS (
SELECT
*,
COALESCE(LEAD(pins, 1) OVER (w), 0) AS next_pins,
COALESCE(LEAD(pins, 2) OVER (w), 0) AS next_next_pins,
roll = 1 AND pins = 10 AS is_strike,
roll = 2 AND (pins + LAG(pins, 1) OVER (w) = 10) AS is_spare,
frame = (max(frame) OVER (PARTITION BY game)) AS is_last_frame
FROM game_rolls
WINDOW w AS (PARTITION BY game ORDER BY game, frame, roll)
),
scores AS (
SELECT
*,
CASE
WHEN is_last_frame THEN pins
WHEN is_strike THEN pins + next_pins + next_next_pins
WHEN is_spare THEN pins + next_pins
ELSE
pins
END
AS score
FROM stats
),
sums AS (
-- Note: This CTE will get optimized away by the query planner.
-- Nothing is calling it, and we can choose which to call in the final query.
SELECT
game,
SUM(score)
FROM scores
GROUP BY game
),
cumulative_scores AS (
SELECT
*,
SUM(score) OVER (w) AS cumulative_score,
CASE
WHEN is_strike THEN 'X'
WHEN is_spare THEN ' /'
ELSE REPEAT(' ', roll - 1) || pins
END display
FROM scores
WINDOW w AS (PARTITION BY game ORDER BY game, frame, roll)
)
SELECT
game,
frame,
display,
cumulative_score
FROM cumulative_scores
;
Results Game 1:
| game | frame | display | cumulative_score |
|---|---|---|---|
| 1 | 1 | X | 30 |
| 1 | 2 | X | 60 |
| 1 | 3 | X | 90 |
| 1 | 4 | X | 120 |
| 1 | 5 | X | 150 |
| 1 | 6 | X | 180 |
| 1 | 7 | X | 210 |
| 1 | 8 | X | 240 |
| 1 | 9 | X | 270 |
| 1 | 10 | X | 280 |
| 1 | 10 | 10 | 290 |
| 1 | 10 | 10 | 300 |
Results Game 2:
| game | frame | display | cumulative_score |
|---|---|---|---|
| 2 | 1 | 1 | 1 |
| 2 | 1 | 0 | 1 |
| 2 | 2 | 1 | 2 |
| 2 | 2 | 0 | 2 |
| 2 | 3 | 1 | 3 |
| 2 | 3 | 0 | 3 |
| 2 | 4 | 1 | 4 |
| 2 | 4 | 0 | 4 |
| 2 | 5 | 1 | 5 |
| 2 | 5 | 0 | 5 |
| 2 | 6 | 1 | 6 |
| 2 | 6 | 0 | 6 |
| 2 | 7 | 1 | 7 |
| 2 | 7 | 0 | 7 |
| 2 | 8 | 1 | 8 |
| 2 | 8 | 0 | 8 |
| 2 | 9 | 1 | 9 |
| 2 | 9 | 0 | 9 |
| 2 | 10 | 1 | 10 |
| 2 | 10 | 0 | 10 |
Results Game 3:
| game | frame | display | cumulative_score |
|---|---|---|---|
| 3 | 1 | 0 | 0 |
| 3 | 1 | / | 10 |
| 3 | 2 | 0 | 10 |
| 3 | 2 | / | 20 |
| 3 | 3 | 0 | 20 |
| 3 | 3 | / | 30 |
| 3 | 4 | 0 | 30 |
| 3 | 4 | / | 40 |
| 3 | 5 | 0 | 40 |
| 3 | 5 | / | 50 |
| 3 | 6 | 0 | 50 |
| 3 | 6 | / | 60 |
| 3 | 7 | 0 | 60 |
| 3 | 7 | / | 70 |
| 3 | 8 | 0 | 70 |
| 3 | 8 | / | 80 |
| 3 | 9 | 0 | 80 |
| 3 | 9 | / | 90 |
| 3 | 10 | 0 | 90 |
| 3 | 10 | / | 100 |
Results Game 4:
I'm putting this in a preformatted text block so you can see the formatting. Markdown tables aren't doing this justice.
|game|frame|display|cumulative_score|
|----|-----|-------|----------------|
| 4| 1|7 | 7|
| 4| 1| 2 | 9|
| 4| 2|X | 28|
| 4| 3|5 | 33|
| 4| 3| 4 | 37|
| 4| 4|8 | 45|
| 4| 4| 1 | 46|
| 4| 5|X | 65|
| 4| 6|6 | 71|
| 4| 6| 3 | 74|
| 4| 7|9 | 83|
| 4| 7| 0 | 83|
| 4| 8|X | 102|
| 4| 9|7 | 109|
| 4| 9| 2 | 111|
| 4| 10|X | 121|
| 4| 10| 10 | 131|
| 4| 10| 8 | 139|
I narrowed the columns a little to make it easier to follow. In the final query, I would include all the columns to let the client decide how to display it.
Conclusion¶
If you wanted to use this using a Business Intelligence (BI) tool, you should create a view for each CTE and then query the final view.
Should bowling be calculated in SQL? I'll leave that up to you. It was a fun exercise to figure out the logic and I hope you enjoyed the ride!