T-SQL Puzzle

Some time ago, I taught a T-SQL class in the UK. At the end of the class, a student named Paul Shipley presented me with a puzzle and asked whether I could find a T-SQL solution for it. I found the puzzle to be interesting and thought others would enjoy working on it as well. In this article, I'll present the puzzle and offer a possible solution in T-SQL. As always, I recommend that before you look at my solution you first try to come up with your own solution; otherwise, you won't enjoy the puzzle as much.

Blockbusters Puzzle

The puzzle that's the focus of this article is related to a TV game show called TV game show called Blockbusters, which ran in the UK in the 1980s and early 1990s. For the purposes of this article, we'll use a modification of the game show.

In our version of the Blockbusters game show, two players take part. We'll call the players the vertical player and the horizontal player. The game involves a board of a certain dimension (e.g., 5 × 5); the tiles are square. (In the original game show, the board is 5 × 4 and the tiles are shaped as hexagons.) The tiles are marked with letters. To determine which player starts, a tile is chosen at random and a toss-up question related to the chosen letter is presented to the players. The player who buzzes first and answers the question correctly gets control over the board.

The player whose turn it is to play chooses the next tile, and the host presents a question related to that letter. If the player answers the question correctly, that tile is marked as his (we'll use the letter V to mark the tile as belonging to the vertical player and H as belonging to the horizontal player). Also, if the player answers the question correctly, he or she gets to choose the next tile. If the current player answers incorrectly, the other player gets a chance to answer the question. If neither player answers the question correctly, the host presents another trivia question related to that letter.

The goal of the game is to complete an unbroken path from one side of the board to the other. The vertical player needs to complete a vertical path (from top to bottom or bottom to top), and the horizontal player needs to complete a horizontal path (from left to right or right to left). As an example, Figure 1 shows a sample game in whichthe horizontal player completed a path (unbroken connection from left to right).

Figure 1: Sample Blockbusters Game

Our challenge is to write a T-SQL solution that checks whether a player completed an unbroken path. For sample data, use the code in Listing 1.

This code creates a table called Blockbusters and populates it with data for tiles that already belong to the two players. Each row in the table holds the tile's row, col, and player (V for horizontal and H for vertical). You can use the following query to present the current state of the game pivoted:

SELECT F.row, [1],[2],[3],[4],[5] FROM (SELECT row, col, player FROM dbo.Blockbusters) AS D PIVOT(MAX(player) FOR col IN ([1],[2],[3],[4],[5])) AS P RIGHT OUTER JOIN (VALUES(1),(2),(3),(4),(5)) AS F(row) ON P.row = F.row;

This query generates the output in Figure 2.

Figure 2: Current State of Game

Your task is to write a user-defined function called IsCompleted that accepts two inputs: @player (V or H) and @size (how many rows in the board for a vertical player, or columns for a horizontal player). The function should return the bit 1 if the player in question completed an unbroken path and 0 otherwise.

Solution to Blockbusters Puzzle

You can find my complete solution with the definition of the IsCompleted function in Listing 2.

The function's code assigns group numbers to the tiles of the input player and stores those values in a table variable called @T. Each unbroken group of tiles gets a unique group number. For example, suppose that the input player is the horizontal one. With the current state of the game, the function assigns group numbers for the tiles, as Figure 3 shows.

Figure 3: Group Assignment for Horizontal Player

The order in which the group numbers are assigned is based on the starting point of the group (based on row, then column order). The group whose first tile is in row 1, column 1 gets group number 1. The group whose first tile comes next (row 1, column 3) gets group number 2. And so on.

The @grp variable is used to keep track of the current group number. It's initialized with 0 and incremented whenever the code starts processing a new group. The code uses the following query to find the first tile in the first group:

SELECT TOP (1) @currow = row, @curcol = col FROM dbo.Blockbusters WHERE player = @player ORDER BY row, col;

Then it loops as long as a first tile of a new group is found (WHILE @currow IS NOT NULL). After the body of the loop is done handling the current group, the code uses the following query to get the first tile of the next group:

SELECT TOP (1) @currow = row, @curcol = col FROM dbo.Blockbusters AS B WHERE NOT EXISTS (SELECT * FROM @T AS T WHERE T.row = B.row AND T.col = B.col) AND player = @player AND ( row = @prvrow AND col > @prvcol OR row > @prvrow ) ORDER BY row, col;

The loop's body is responsible for finding all related tiles of the current group. It starts by incrementing the variables @grp and @iteration. Remember that @grp holds the current group number. Each group is handled by a number of iterations; each iteration deals with the neighbors of the tiles in the previous iteration that weren't already handled. The variable @iteration is used to associate tiles with the iteration in which they were handled.

The code continues by inserting into the table variable @T the information about the first tile in the current group:

Next, the code uses an inner loop that in each iteration looks for neighbors of the tiles from the previous round. It iterates as long as the tiles are found in the previous round (@@ROWCOUNT > 0).

The inner loop's body increments the @iteration variable by 1. It then uses the following code to insert into the table variable @T direct neighbors of the tiles handled in the previous iteration that weren't handled before:

Finally, after all tiles are assigned with group numbers, the code uses a CASE expression that checks whether the input player completed an unbroken path or not. It does so by grouping the tiles by the group number and computing the minimum and maximum column numbers for a horizontal player and the minimum and maximum row numbers for a vertical player. If the minimum is equal to 1 and the maximum is equal to @size, the player completed an unbroken path. In such a case, the CASE expression returns a 1; otherwise it returns a 0.

The code generates the output in Figure 4, correctly indicating that the horizontal player completed an unbroken path and the vertical player didn't.

Figure 4: State of Players

Sharpen Your Skills

Solving puzzles with T-SQL is fun and allows you to sharpen your T-SQL skills. I'd like to thank Paul for sharing this puzzle with me. I hope you had fun trying to solve it. If you're looking for another challenging puzzle, try to solve the following: Write a function that checks whether the state of the game is such that a player cannot complete an unbroken path.

Discuss this Article 4

Since the size of the board is fixed and there are a fixed number of winning paths, I'd probably pre-calculate and encode all possible winning paths, then see if one of them is satisfied. It would be faster than manually walking each path each time the state of the board changes.
Do the hard work up front, then benefit from it with each state change.
Think of it as an OLAP-ish approach to the problem.

The reason for the Hexagons in the original game grid is that this was based on the game Hex, (http://en.wikipedia.org/wiki/Hex_game). It was invented independently by John Nash and Piet Hein. It had a fad in Europe and there were hex problems in daily newspapers.
When the sides of the grid are equal, the game favors the first player, but there is a winning strategy for non-symmetrical n×m boards. Obviously, a completed path by one player blocks the other player. John Nash proved there can be no ties.

I have a solution,but I cannot post it because of size limits. It is graph theory based . Technically, ROW is a reserved word in ANSI/ISO Standard SQL and should not be a column name. I want to change the DDL to remove NULLs and the DML to add a full grid into the table. Hey, it is only 25 cells in the grid.
CREATE TABLE Blockbusters
(row SMALLINT NOT NULL
CHECK (row BETWEEN 1 AND 5),
col SMALLINT NOT NULL
CHECK (col BETWEEN 1 AND 5),
player CHAR(1) NOT NULL
CHECK (player IN ('V', 'H', ' ')),
PRIMARY KEY(row, col),
UNIQUE (player, row, col));
DELETE FROM Blockbusters;
INSERT INTO Blockbusters(row, col, player)
VALUES (1, 1, 'H'), (1, 3, 'H'), (1, 4, 'H'), (1, 5, 'V'),
(2, 2, 'H'), (2, 3, 'H'), (2, 4, 'H'), (2, 5, 'V'),
(3, 2, 'H'), (3, 3, 'V'), (3, 4, 'V'), (3, 5, 'V'),
(4, 1, 'H'), (4, 2, 'H'), (4, 3, 'V'), (4, 4, 'H'), (4, 5, 'V'),
(5, 2, 'H'), (5, 3, 'H'), (5, 4, 'H'), (5, 5, 'H'),
-- add empty cells
(1, 2, ' '), (2, 1, ' '), (3, 1, ' '), (5, 1, ' ');
I am also going to change your problem statement slightly. Instead of trying to write a BIT function (ugh!) to find a winner, let's write code to find, if a path exists, for one of the players; call them Mr. Horizontal and Ms. Vertical after the gender convention for players in Game Theory.
But what we need is Graph Theory since this is a path problem. By symmetry, we can convert a solution for Mr. Horizontal to one for or Ms. Vertical with a simple rotation. I will leave that as a exercise for the reader. There are there three outcomes; Ms. Vertical wins and Mr. Horizontal loses, Mr. Horizontal wins and Ms. Vertical loses or nobody wins. The first two cases are easy to imagine; the winning path of one player blocks all possible paths of the other player. An example of the third case might be a checker board of 'H' and 'V' tokens, if both players co-operated.
If Mr. Horizontal has a win, then he has
1) At least five tokens on the grid.
2) At least one token in each of the five colum

What about a rowset based solution like this:
CREATE FUNCTION dbo.IsCompleted(@player AS CHAR(1), @size AS INT) RETURNS BIT
AS
BEGIN
DECLARE @i bit;
set @i=0;
IF (@player='H')
BEGIN
WITH t AS
(select b.row,b.col,0 as prev_row,0 as prev_col
from Blockbusters b
where
player='H'
and b.col=1
UNION ALL
SELECT b.row,b.col,t.row, t.col
from Blockbusters b
INNER JOIN t ON (b.row<>t.prev_row or b.col<> t.prev_col) and ((b.row in (t.row-1,t.row+1) and b.col=t.col) OR (b.row=t.row AND b.col=t.col+1) )
WHERE b.player='H'
)
select TOP(1) @i=1 from t
where t.col=@size
END
ELSE IF (@player='V')
BEGIN
WITH t AS
(select b.row,b.col,0 as prev_row,0 as prev_col
from Blockbusters b
where
player='V'
and b.row=1
UNION ALL
SELECT b.row,b.col,t.row, t.col
from Blockbusters b
INNER JOIN t ON (b.row<>t.prev_row or b.col<> t.prev_col) and ((b.col in (t.col-1,t.col+1) and b.row=t.row) OR (b.col=t.col AND b.row=t.row+1) )
WHERE b.player='V'
)
select TOP(1) @i=1 from t
where t.row=@size
END
RETURN @i
END
GO

From the Blogs

The quest for the Golden Record to achieve a single, accurate and complete version of a customer record is worth the pursuit to attain survivorship. Record matching and consolidation are only the beginning. Melissa Data takes a new approach. Learn how to apply intelligent rules based on reference data to make smarter and better decisions for data cleansing....More

On SQL Servers where Availability Groups (or Mirroring) isn’t in play, I typically recommend keeping a combination of on-box backups along with copying said backups off-box as well. Obviously, keeping databases AND backups on the SAME server is the metaphorical equivalent of putting all of your eggs in one basket – and therefore something you should avoid like the plague....More

One of the biggest strengths of AlwaysOn Availability Groups is that they allow DBAs to address both high availability and disaster recovery concerns from a single set of tooling or interfaces. But, this doesn’t mean that you won’t still need backups....More