Puzzles in SQL

The other day our office manager brought in an old style puzzle, that I used to do as a child.

After thinking about how I used to solve them, decided to let SQL do the grunt work for me:

I used lower case to mark the tail of the plane, and upper case to front of the plane.

The cards are encoded as these id’s

| 0 | 1 | 2 |
| 3 | 4 | 5 |
| 6 | 7 | 8 |

1
2
3
4
5
6
7
8
9
10
11
12
with data(id,a,b,c,d) as (
select * from values
(0, 'b', 'w', 'B', 'Y' ),
(1, 'y', 'g', 'B', 'W' ),
(2, 'w', 'G', 'B', 'y' ),
(3, 'b', 'y', 'G', 'W' ),
(4, 'g', 'y', 'G', 'W' ),
(5, 'b', 'w', 'G', 'Y' ),
(6, 'B', 'Y', 'g', 'w' ),
(7, 'g', 'w', 'B', 'Y' ),
(8, 'w', 'y', 'B', 'G' )
)

then swapped those values to an encoding where “a correct match” sums to 10

1
2
3
4
5
6
7
8
), encode as (
select id,
decode(a, 'b', 1, 'B', 9, 'g', 2, 'G', 8, 'y', 3, 'Y', 7, 'w', 4, 'W', 6 ) as a,
decode(b, 'b', 1, 'B', 9, 'g', 2, 'G', 8, 'y', 3, 'Y', 7, 'w', 4, 'W', 6 ) as b,
decode(c, 'b', 1, 'B', 9, 'g', 2, 'G', 8, 'y', 3, 'Y', 7, 'w', 4, 'W', 6 ) as c,
decode(d, 'b', 1, 'B', 9, 'g', 2, 'G', 8, 'y', 3, 'Y', 7, 'w', 4, 'W', 6 ) as d
from data
)

and from there made the permutations of the 4 rotations of each tile

1
2
3
4
5
6
7
8
9
), tiles_rot as (
select id
,decode((column1+0) % 4, 0 , a, 1, b, 2, c, 3, d) as a
,decode((column1+1) % 4, 0 , a, 1, b, 2, c, 3, d) as b
,decode((column1+2) % 4, 0 , a, 1, b, 2, c, 3, d) as c
,decode((column1+3) % 4, 0 , a, 1, b, 2, c, 3, d) as d
,column1 as r
from encode cross join (values(0),(1),(2),(3)) as v
)

then wove those together with the don’t repeat the same tile rules, and enforce the “must match existing tiles on edges” rules for the 12 joins as the tiles are placed:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
select 
a.id || ' (0)' as a,
b.id || ' (' || b.r || ')' as b,
c.id || ' (' || c.r || ')' as c,
d.id || ' (' || d.r || ')' as d,
e.id || ' (' || e.r || ')' as e,
f.id || ' (' || f.r || ')' as f,
g.id || ' (' || g.r || ')' as g,
h.id || ' (' || h.r || ')' as h,
i.id || ' (' || i.r || ')' as i
from encode as a
join tiles_rot as b
on b.id != a.id
and a.b + b.d = 10
join tiles_rot as c
on c.id != a.id and c.id != b.id
and b.c + c.a = 10
join tiles_rot as d
on d.id != a.id and d.id != b.id and d.id != c.id
and a.c + d.a = 10
and c.d + d.b = 10
join tiles_rot as e
on e.id != a.id and e.id != b.id and e.id != c.id
and e.id != d.id
and d.d + e.b = 10
join tiles_rot as f
on f.id != a.id and f.id != b.id and f.id != c.id
and f.id != d.id and f.id != e.id
and f.c +e.a = 10 and f.b + a.d = 10
join tiles_rot as g
on g.id != a.id and g.id != b.id and g.id != c.id
and g.id != d.id and g.id != e.id and g.id != f.id
and f.a + g.c = 10
join tiles_rot as h
on h.id != a.id and h.id != b.id and h.id != c.id
and h.id != d.id and h.id != e.id and h.id != f.id
and h.id != g.id
and h.d + g.b = 10 and h.c + a.a = 10
join tiles_rot as i
on i.id != a.id and i.id != b.id and i.id != c.id
and i.id != d.id and i.id != e.id and i.id != f.id
and i.id != g.id and i.id != h.id
and i.d + h.b = 10 and i.c + b.a = 10
A B C D E F G H I
1 (0) 8 (0) 0 (0) 3 (0) 7 (0) 2 (3) 6 (3) 5 (1) 4 (1)
1 (0) 8 (0) 0 (0) 3 (0) 6 (2) 2 (3) 7 (1) 5 (1) 4 (1)
3 (0) 5 (0) 7 (0) 4 (0) 0 (0) 6 (2) 2 (3) 1 (0) 8 (0)
3 (0) 5 (0) 6 (2) 4 (0) 0 (0) 7 (0) 2 (3) 1 (0) 8 (0)

and it takes just ~1 second to run in Snowflake on an extra small instance.

and the layout is in this pattern:

| C | D | E |
| B | A | F |
| I | H | G |

looking at the four solutions, they are two base solutions where two cards can be swapped.. is interesting.