CROSS – Dave + SQL Server = Ingenious SQL….

“Winner Winner Chicken Dinner”, and “Vegas, Vegas, Vegas” are quotes from two of my favorite Las Vegas movies. It’s no secret that my wife Kristin and I love Las Vegas, it’s also no secret that I love coming up with ingenious database solutions to random problems. So I was thinking, what it would take to create a simple 3 wheel slot machine in SQL Server. Is SQL Server the right place to put slot machine business logic? Ummmm, probably not, but that has never stopped me before. In this blog post I am going to build a 3 wheel slot simulation using small data set that represents the possible combinations that could appear and a random selection process to determine a pull outcome. I will also add a small table to track the results of each pull including a running balance in a players table.

Slot machine details

A standard 3 wheel slot with 20 stopping position yields 8000 possible result combinations (20³ = 8000). If there is only one jackpot symbol on each of the wheels, this means there is a 1 in 8000 chance of hitting the jackpot. If there are 5 empty spaces per wheel, there are 125 possibilities to hit all empty spaces. The simple math is multiply the count of a specific symbol on each wheel. Example, if there are five single bars on wheel number one, two 7’s on wheel number two, and one Wild on wheel three, thus 5 X 2 X 1 hence there are 10 opportunities for this (B-7-W) combination to appear. The total number of distinct combinations can be found by multiplying the count of different possible symbols per wheel. We have 6 different symbols on each wheel, thus 6 X 6 X 6 = 216 possibilities.

Slot machine data set

Armed with the combination logic we can build out a dataset for all 8000 possible combinations. Using an opportunity field, we can store the number of opportunities that each specific combination can appear. See the combinations table for full data set and opportunity count. Combining the opportunity field with a numbers (virtual-auxiliary-table-numbers) table, thanks @ItzikBenGan, we can build out all 8000 combinations. Once we have all 8000 possible combinations we can randomly select a single combination.

Slot machine SQL logic

For this simulation we will need a few tables to store the combinations, the results log for our players, and a table for our players. The combinations table will store symbol for each column, a winning amount for the combination, and the number of opportunities the combination will appear. The results log will store the resulting combination, the winning amount, the player, and a timestamp the slot machine was played. The players table will store the players name and the available funds for playing the slot machine.

Table definitions

Combinations Table

CREATE TABLE Combinations (
  CombinationID INT PRIMARY KEY IDENTITY(1,1) NOT NULL
, Reel1 VARCHAR(5) NOT NULL
, Reel2 VARCHAR(5) NOT NULL
, Reel3 VARCHAR(5) NOT NULL
, WinningAmount INT NOT NULL
, Opportunities INT NOT NULL
) ON  [PRIMARY];

ResultsLog Table

CREATE TABLE ResultsLog (
ResultsLogID INT PRIMARY KEY IDENTITY(1,1) NOT NULL
, PlayerID INT NOT NULL 
, Reel1 VARCHAR(5) NOT NULL
, Reel2 VARCHAR(5) NOT NULL
, Reel3 VARCHAR(5) NOT NULL
, WinningAmount INT NOT NULL
, ResultDate DATETIME NOT NULL
);

Player Table

CREATE TABLE Player (
PlayerID INT PRIMARY KEY IDENTITY(1,1) NOT NULL, 
, Name VARCHAR(100) NOT NULL
, AvailableFunds INT NOT NULL
);

Building the Combinations Dataset

Using a CROSS APPLY between the combinations table data and Itzik Ben-Gan’s numbers common table expression will yield 8000 total possible combinations.

WITH L0 AS (SELECT 1 AS C UNION ALL SELECT 1 AS O) , -- 2 rows
     L1 AS (SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B), --4 rows
     L2 AS (SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B),-- 16 rows
     L3 AS (SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B), -- 256 rows
     Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number FROM L3)
SELECT  Reel1
        , Reel2
        , Reel3
        , WinningAmount
FROM    Combinations cbn
CROSS APPLY Nums nms
WHERE   nms.Number <= cbn.Opportunities;

Selecting a winning combination

Using TSQL’s random function we can select a random winning combination. The RAND function takes a seed value to start the randomization process, if the same seed is provided, the same random value will be returned. So we will use the NEWID and CHECKSUM functions to gather a random seed value. To get a value between 0 – 7999, we can multiply the RAND function result with 8000 and cast as an integer. Adding one gives us a random value between 1 and 8000.

SELECT CAST(RAND(CHECKSUM(NEWID())) * 8000 AS INT) + 1

Adding a bit of SQL ingenuity we can order the combinations dataset by the random value function. This will completely randomize the combinations dataset. All that’s left to do is select a single record, say the first record.

WITH L0 AS (SELECT 1 AS C UNION ALL SELECT 1 AS O) , -- 2 rows
     L1 AS (SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B), --4 rows
     L2 AS (SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B),-- 16 rows
     L3 AS (SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B), -- 256 rows
     Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number FROM L3)
SELECT TOP 1
        Reel1
      , Reel2
      , Reel3
      , WinningAmount
FROM    Combinations cbn
CROSS APPLY Nums nms
WHERE   nms.Number <= cbn.Opportunities
ORDER BY CAST(RAND(CHECKSUM(NEWID())) * 8000 AS INT) + 1;

Time to play the slot

Running the query 5 times yielded the following results.

Reel1	Reel2	Reel3
_	BB	B
BBB	7	BB
B	B	_
B	W	_
7	BBB	_

Hmmmm… I didn’t win a single time… I think I successfully created a slot machine.

Log the results

All that’s left is to save the results of each spin and update the players available funds. A simple insert into the Results log, including the player, reels combination, winning amount and time-stamp will handle the logging side.

DECLARE @Reel1 NVARCHAR(5);
DECLARE @Reel2 NVARCHAR(5);
DECLARE @Reel3 NVARCHAR(5);
DECLARE @WinningAmount INT;
 
WITH L0 AS (SELECT 1 AS C UNION ALL SELECT 1 AS O) , -- 2 rows
     L1 AS (SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B), --4 rows
     L2 AS (SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B),-- 16 rows
     L3 AS (SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B), -- 256 rows
     Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number FROM L3)
SELECT TOP 1
        @Reel1 = Reel1
      , @Reel1 = Reel2
      , @Reel1 = Reel3
      , @WinningAmount = WinningAmount
FROM    Combinations cbn
CROSS APPLY Nums nms
WHERE   nms.Number <= cbn.Opportunities
ORDER BY CAST(RAND(CHECKSUM(NEWID())) * 8000 AS INT) + 1;
 
INSERT INTO dbo.ResultsLog (PlayerID, Reel1, Reel2, Reel3, WinningAmount, ResultDate)
VALUES  (
  	 529
	,@Reel1
	,@Reel2
	,@Reel3
	,@WinningAmount
	,GETDATE()
);

A simple update will handle the players available funds.

UPDATE  Player
SET     AvailableFunds = AvailableFunds + @WinningAmount - 1
WHERE   PlayerID = 529;

Summary

Obviously this is a simplified implementation of a 3 reel slot machine but it meets the statistical opportunities for all combinations based on the number of symbols per reel. So using some ingenious ideas, a numbers table from Itzik Ben-Gan, and standard T-SQL functionality, we have a functional SQL slot machine that logs its results and keeps players hard earned virtual money. All that’s left to do is book a trip to Las Vegas…

DONE! combacksoon

Introduction:

In my very first Technical Blog I am going to show how one can accomplish the Fizz Buzz Challenge a few different ways using T-SQL. Fizz Buzz is a popular interview question that will allow an interviewee to demonstrate basic programming knowledge. This is normally done in a programming language such as C# or Java. But today we are going to implement Fizz Buzz using T-SQL…

Background:

A friend, Rick Krueger ( @DataOgre | premierapps.com ), and I have recently decided to give back to the SQL Community by starting to Tweet, Blog, and Present at Local PASS Meetings as well as SQL Saturday events. We had come up with a potential presentation where we needed a Developer and a DBA, the question was who is the DBA and who is the Developer. So a week or so had passed and I found myself with a locked account and a significant amount of wasted time with the help desk.

Rick noticed my frustration and asked the following:

(rick) Why on earth did they need to log onto your box to reset something on the server?
(me) Not sure, I guess he didnt believe me when I said my account was locked and he had to try for himself?
(rick) Did you try FizzBin?
(rick) http://www.hanselman.com/blog/FizzBinTheTechnicalSupportSecretHandshake.aspx
(me) Nope, but I wish I would have…
(rick) Ever hear of FizzBuzz a good interview question…
(rick) How would you do on that one?

Now any good developer will know who Scott Hanselman is and has followed his work with .Net and MVC… And being that I came from the development world I know who Hanselman is. So I quickly created a C#.net example and sent it off to Rick.

Fizz Buzz:

Output the numbers from 1 to 100.
When a number is a multiple of three output “Fizz”.
When a number is a multiple of five output “Buzz”.
When a number is a multiple of both three and five output “FizzBuzz”
When a number is not a multiple of either three or five output the number

C# Code of FizzBuzz:

for(int i = 1; i <= 100; i++){
   if(i % 3 == 0) 
      Console.Write("Fizz "); 
   if(i % 5 == 0) 
      Console.Write("Buzz "); 
   if((i % 3 > 0) && (i % 5 > 0)) 
      Console.Write(i); 
   Console.WriteLine(""); 
}

Bamboozled:

So I asked Rick how he would have implemented the same code to which he simply replied ‘T-SQL’. Without missing a beat, he proceeded to tell me that we now know who the developer and who the DBA is for our presentations…. I was bamboozled into writing C#.net code…

T-SQL:

Below are some T-SQL examples of the Fizz Buzz code. Obviously there are many more ways to write it, but here are a couple I wrote to redeem myself in the DB community…

The example below implements a while loop that populates a table, using the Fizz Buzz logic.

DECLARE @FizzBuzzTable TABLE(Number int, FizzBuzz varchar(10));
DECLARE @i int = 1;
WHILE @i <= 100
BEGIN
 INSERT INTO @FizzBuzzTable
   (Number, FizzBuzz)
 SELECT @i, FizzBuzz = CASE WHEN @i % 3 = 0 AND @i % 5 = 0 THEN 'Fizz Buzz'
   WHEN @i % 3 = 0 THEN 'Fizz'
   WHEN @i % 5 = 0 THEN 'Buzz'
   ELSE CAST(@i AS varchar(3)) END
 SET @i += 1
END
SELECT Number, FizzBuzz FROM @FizzBuzzTable FBT

Using a while loop to insert records may not be the best approach, so the example below implements a recursive CTE to gain 100 values, then applies the Fizz Buzz Logic query.

 ;WITH FizzBuzzTable( Number ) AS (
 SELECT 1 UNION ALL
 SELECT 1 + Number FROM FizzBuzzTable WHERE Number < 100
 )
 SELECT FizzBuzz= CASE WHEN Number % 3 = 0 AND Number % 5 = 0 THEN 'Fizz Buzz'
   WHEN Number % 3 = 0 THEN 'Fizz'
   WHEN Number % 5 = 0 THEN 'Buzz'
   ELSE CAST(Number AS varchar(3)) END
 FROM FizzBuzzTable

Using a recursive CTE can have performance issues, so the example below uses SQL written by Ben Itzak to gain 256 values, then applies the Fizz Buzz Logic query.

;WITH L1 AS (SELECT V1 = 1 UNION ALL SELECT V1 = 1) -- 2
 , L2 AS (SELECT V1 = 1 FROM L1 A CROSS APPLY L1 B)  -- 4
 , L3 AS (SELECT V1 = 1 FROM L2 A CROSS APPLY L2 B)  -- 16
 , L4 AS (SELECT V1 = 1 FROM L3 A CROSS APPLY L3 B)  -- 256
 , FizzBuzzTable AS (SELECT TOP 100 Number = ROW_NUMBER() OVER (ORDER BY V1) FROM L4)
 SELECT FizzBuzz = CASE WHEN Number % 3 = 0 AND Number % 5 = 0 THEN 'Fizz Buz'
   WHEN Number % 3 = 0 THEN 'Fizz'
   WHEN Number % 5 = 0 THEN 'Buzz'
   ELSE CAST(Number AS varchar(3)) END
 FROM FizzBuzzTable

Dave + SQL Server = Ingenious SQL….

SQL Thoughts from @IngeniousSQL

Tag: CROSS

Winner Winner Chicken Dinner

Bamboozled by Fizz Buzz