Post date: Mar 12, 2017 1:55:53 PM
"Given two dice each with six digits between 0 and 9, and allowing 6 and 9 to be flipped to get the other number, how many distinct combinations of dice will produce all square numbers below 100?"
This dice setup is weird and confusing, but counting distinct combinations is one of the things SQL excels at, so it's not extremely difficult.
CREATE TABLE #Digits (i INT PRIMARY KEY NOT NULL)
INSERT INTO #Digits VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
-- Pre-split our table of squares. No point in doing this at runtime.
CREATE TABLE #Squares (i TINYINT NOT NULL, a TINYINT NOT NULL, b TINYINT NOT NULL)
INSERT INTO #Squares
VALUES (1, 0, 1),
(4, 0, 4),
(9, 0, 9),
(16, 1, 6),
(25, 2, 5),
(36, 3, 6),
(49, 4, 9),
(64, 6, 4),
(81, 1, 8)
-- The order of the dice doesn't matter.
INSERT INTO #Squares (i, a, b)
SELECT i, b, a
FROM #Squares
-- Build our table with every combination of dice.
-- If there is a 6 in the die, we make a magical 7th square that contains the flip. This allows a single item to do double duty.
-- (Just one of several ways to do this. It was the simplest.)
; WITH Dice (d1, d2, d3, d4, d5, d6) AS
(
SELECT d1.i, d2.i, d3.i, d4.i, d5.i, d6.i
FROM #Digits d1
INNER JOIN #Digits d2
ON d2.i > d1.i
INNER JOIN #Digits d3
ON d3.i > d2.i
INNER JOIN #Digits d4
ON d4.i > d3.i
INNER JOIN #Digits d5
ON d5.i > d4.i
INNER JOIN #Digits d6
ON d6.i > d5.i
)
SELECT *
, CASE WHEN 6 IN (d1, d2, d3, d4, d5, d6) AND 9 NOT IN (d1, d2, d3, d4, d5, d6) THEN 9
WHEN 9 IN (d1, d2, d3, d4, d5, d6) AND 6 NOT IN (d1, d2, d3, d4, d5, d6) THEN 6
END AS d7
INTO #Dice
FROM Dice
-- This query gives us every combination of dice that can be used to build each square.
-- We count the number of squares that can be built by each combination. If there are 9, it is a match, and must be returned.
-- Note that this doubles the results, because die 1 and die 2 are identical. They are perfect mirror images of each other.
-- So if we care about the truly distinct count, we must divide the result by 2.
; WITH Dice (d1, d2, d3, d4, d5, d6, d7, d8, d9, d10, d11, d12) AS
(
SELECT d1.d1, d1.d2, d1.d3, d1.d4, d1.d5, d1.d6, d2.d1, d2.d2, d2.d3, d2.d4, d2.d5, d2.d6
FROM #Dice d1
CROSS JOIN #Dice d2
INNER JOIN #Squares s
ON s.a IN (d1.d1, d1.d2, d1.d3, d1.d4, d1.d5, d1.d6, d1.d7)
AND s.b IN (d2.d1, d2.d2, d2.d3, d2.d4, d2.d5, d2.d6, d2.d7)
GROUP BY d1.d1, d1.d2, d1.d3, d1.d4, d1.d5, d1.d6, d2.d1, d2.d2, d2.d3, d2.d4, d2.d5, d2.d6
HAVING COUNT(DISTINCT s.i) = 9
)
SELECT COUNT(*)/2
FROM Dice
DROP TABLE #Dice
DROP TABLE #Squares
DROP TABLE #Digits