Snippets

Mariusz Krynski tic-tac-toe.sql

Updated by Mariusz Kryński
View revision

File tic-tac-toe.sql Modified

  • Ignore whitespace
  • Hide word diff 
 with recursive rnd_move(move) as (
         select *, random() rnd from generate_series(1, 9) move
 ), winning_positions(a, b, c) as (
-    values (1, 2, 3), (4, 5, 6), (7, 8, 9),
-           (1, 4, 7), (2, 5, 8), (3, 6, 9),
-           (1, 5, 9), (3, 5, 7)
+    values (1, 2, 3), (4, 5, 6), (7, 8, 9), -- rows
+           (1, 4, 7), (2, 5, 8), (3, 6, 9), -- cols
+           (1, 5, 9), (3, 5, 7)             -- diagonals
 ), game as (
     select 'O' as who_next, ARRAY['.', '.', '.', '.', '.', '.', '.', '.', '.'] as board
     union
Updated by Mariusz Kryński
View revision

File snippet.sql Deleted

  • Ignore whitespace
  • Hide word diff 
-with recursive rnd_move(move) as (
-        select *, random() rnd from generate_series(1,9) move
-), winning_positions(winning_position) as (
-    values (ARRAY[1, 2, 3]), (ARRAY[4, 5, 6]), (ARRAY[7, 8, 9]),
-           (ARRAY[1, 4, 7]), (ARRAY[2, 5, 8]), (ARRAY[3, 6, 9]),
-           (ARRAY[1, 5, 9]), (ARRAY[3, 5, 7])
-), game as (
-    select ARRAY[]::int[] moves
-    union 
-    (
-        select moves || ARRAY[move]
-        from game, rnd_move
-        where not move = any(moves) 
-        order by rnd limit 1
-    )
-), game_with_xo_moves as (
-    select moves[1:1] || moves[3:3] || moves[5:5] || moves[7:7] || moves[9:9] as x_moves,
-           moves[2:2] || moves[4:4] || moves[6:6] || moves[8:8] as o_moves
-    from game
-), game_with_winner as  (
-    select x_moves, o_moves, winning_position, lag(winning_position is not null) over () as win_pos_lag
-    from game_with_xo_moves left join winning_positions on x_moves @> winning_position or o_moves @> winning_position
-), final_game as (
-    select x_moves, o_moves, winning_position from game_with_winner where not win_pos_lag
-) select case when 1 = ANY(x_moves) then 'X' when 1 = ANY(o_moves) then 'O' else '.' end ||
-         case when 2 = ANY(x_moves) then 'X' when 2 = ANY(o_moves) then 'O' else '.' end ||
-         case when 3 = ANY(x_moves) then 'X' when 3 = ANY(o_moves) then 'O' else '.' end || chr(10) ||
-         case when 4 = ANY(x_moves) then 'X' when 4 = ANY(o_moves) then 'O' else '.' end ||
-         case when 5 = ANY(x_moves) then 'X' when 5 = ANY(o_moves) then 'O' else '.' end ||
-         case when 6 = ANY(x_moves) then 'X' when 6 = ANY(o_moves) then 'O' else '.' end || chr(10) ||
-         case when 7 = ANY(x_moves) then 'X' when 7 = ANY(o_moves) then 'O' else '.' end ||
-         case when 8 = ANY(x_moves) then 'X' when 8 = ANY(o_moves) then 'O' else '.' end ||
-         case when 9 = ANY(x_moves) then 'X' when 9 = ANY(o_moves) then 'O' else '.' end || chr(10) as board,
-         case when x_moves @> winning_position then 'X wins' 
-              when o_moves @> winning_position then 'O wins'
-         end as winner
-  from final_game g;

File tic-tac-toe.sql Added

  • Ignore whitespace
  • Hide word diff 
+with recursive rnd_move(move) as (
+        select *, random() rnd from generate_series(1, 9) move
+), winning_positions(a, b, c) as (
+    values (1, 2, 3), (4, 5, 6), (7, 8, 9),
+           (1, 4, 7), (2, 5, 8), (3, 6, 9),
+           (1, 5, 9), (3, 5, 7)
+), game as (
+    select 'O' as who_next, ARRAY['.', '.', '.', '.', '.', '.', '.', '.', '.'] as board
+    union 
+    (
+        select case when who_next = 'X' then 'O' else 'X' end as who_next,
+               board[:move-1] || who_next || board[move+1:]
+        from game, rnd_move where board[move] = '.' order by rnd limit 1
+    )
+), game_with_winner as (
+    select *, lag(a is not null) over () as finished, lag(who_next) over () as who
+    from game left join winning_positions on
+        board[a] != '.' and board[a] = board[b] and board[a] = board[c]
+)
+select array_to_string(board[1:3] || chr(10) || board[4:6] || chr(10) || board[7:9] || chr(10), '') board,
+       case when a is not null then who || ' wins' end as winner
+from game_with_winner where not finished;
Updated by Mariusz Kryński
View revision

File snippet.sql Modified

  • Ignore whitespace
  • Hide word diff 
 with recursive rnd_move(move) as (
         select *, random() rnd from generate_series(1,9) move
 ), winning_positions(winning_position) as (
-    values (ARRAY[1, 2, 3]), (ARRAY[4, 5, 6]), (ARRAY[7, 8, 9]), (ARRAY[1, 4, 7]), (ARRAY[2, 5, 8]), (ARRAY[3, 6, 9])
+    values (ARRAY[1, 2, 3]), (ARRAY[4, 5, 6]), (ARRAY[7, 8, 9]),
+           (ARRAY[1, 4, 7]), (ARRAY[2, 5, 8]), (ARRAY[3, 6, 9]),
+           (ARRAY[1, 5, 9]), (ARRAY[3, 5, 7])
 ), game as (
     select ARRAY[]::int[] moves
     union
Created by Mariusz Kryński
View revision

File snippet.sql Added

  • Ignore whitespace
  • Hide word diff 
+with recursive rnd_move(move) as (
+        select *, random() rnd from generate_series(1,9) move
+), winning_positions(winning_position) as (
+    values (ARRAY[1, 2, 3]), (ARRAY[4, 5, 6]), (ARRAY[7, 8, 9]), (ARRAY[1, 4, 7]), (ARRAY[2, 5, 8]), (ARRAY[3, 6, 9])
+), game as (
+    select ARRAY[]::int[] moves
+    union 
+    (
+        select moves || ARRAY[move]
+        from game, rnd_move
+        where not move = any(moves) 
+        order by rnd limit 1
+    )
+), game_with_xo_moves as (
+    select moves[1:1] || moves[3:3] || moves[5:5] || moves[7:7] || moves[9:9] as x_moves,
+           moves[2:2] || moves[4:4] || moves[6:6] || moves[8:8] as o_moves
+    from game
+), game_with_winner as  (
+    select x_moves, o_moves, winning_position, lag(winning_position is not null) over () as win_pos_lag
+    from game_with_xo_moves left join winning_positions on x_moves @> winning_position or o_moves @> winning_position
+), final_game as (
+    select x_moves, o_moves, winning_position from game_with_winner where not win_pos_lag
+) select case when 1 = ANY(x_moves) then 'X' when 1 = ANY(o_moves) then 'O' else '.' end ||
+         case when 2 = ANY(x_moves) then 'X' when 2 = ANY(o_moves) then 'O' else '.' end ||
+         case when 3 = ANY(x_moves) then 'X' when 3 = ANY(o_moves) then 'O' else '.' end || chr(10) ||
+         case when 4 = ANY(x_moves) then 'X' when 4 = ANY(o_moves) then 'O' else '.' end ||
+         case when 5 = ANY(x_moves) then 'X' when 5 = ANY(o_moves) then 'O' else '.' end ||
+         case when 6 = ANY(x_moves) then 'X' when 6 = ANY(o_moves) then 'O' else '.' end || chr(10) ||
+         case when 7 = ANY(x_moves) then 'X' when 7 = ANY(o_moves) then 'O' else '.' end ||
+         case when 8 = ANY(x_moves) then 'X' when 8 = ANY(o_moves) then 'O' else '.' end ||
+         case when 9 = ANY(x_moves) then 'X' when 9 = ANY(o_moves) then 'O' else '.' end || chr(10) as board,
+         case when x_moves @> winning_position then 'X wins' 
+              when o_moves @> winning_position then 'O wins'
+         end as winner
+  from final_game g;
HTTPS SSH

You can clone a snippet to your computer for local editing. Learn more.