conway's game of life in SQL (postgresql) - http://en.wikipedia.org/wiki/Conway%27s_Game_of_Life

conway.sql

with recursive generation1(x,y) as (   --the initial board setup
  select 2, 3
  union
  select 3, 3
  union
  select 4, 3
), 
game(n, x, y) as (
  select 1, x, y from generation1 -- generation 1 is initial board setup
  union all
  select n+1, new_x, new_y from   -- generation n+1
  (
    select n, x+offset_x new_x, y+offset_y new_y, max(self) over (partition by n+1, x+offset_x, y+offset_y) cell_was_already_alive 
    from game
      join (
        select x.n offset_x, y.n offset_y, case when x.n = 0 and y.n = 0 then 1 else 0 end self 
        from (
          select generate_series(-1, 1) n) x join (select generate_series(-1, 1) n) y on 1=1 --join 2 row generators to get 9 pairs
      ) offsets_to_neighbours_and_self on 1=1
    where n < 100
  ) all_impacts
  group by n+1, new_x, new_y, cell_was_already_alive -- from all impacts back to cells
  having (cell_was_already_alive=1 and count(*) < 5 and count(*) > 2) or count(*) = 3 --decide if cell is alive
              
)
select * from game where n = 4; --select generation 4

prettyprint.sql

with conway (x, y) as (
    with recursive
        initial_state(row) as (
            values ('. X X'),
                   ('X X .'),
                   ('. X .')
        ),
        generation1 as (
            select x, y
            from (
                     select x, generate_subscripts(a, 1) y, a
                     from (
                              select row_number() over () x, string_to_array(row, ' ') a
                              from initial_state) arrays) coordinates_of_live_cells
            where a[y] = 'X'),
        game(n, x, y) as (
            select 1, x, y
            from generation1 -- generation 1 is initial board setup
            union all
            select n + 1, new_x, new_y
            from -- generation n+1
                 (
                     select n,
                            x + offset_x                                                    new_x,
                            y + offset_y                                                    new_y,
                            max(self) over (partition by n + 1, x + offset_x, y + offset_y) cell_was_already_alive
                     from game
                              join (
                         select x.n offset_x, y.n offset_y, case when x.n = 0 and y.n = 0 then 1 else 0 end self
                         from (
                                  select generate_series(-1, 1) n) x
                                  join (select generate_series(-1, 1) n) y on 1 = 1 -- join 2 row generators to get 9 pairs
                     ) offsets_to_neighbours_and_self on 1 = 1
                     where n < 100
                 ) all_impacts
            group by n + 1, new_x, new_y, cell_was_already_alive -- from all impacts back to cells
            having (cell_was_already_alive = 1 and count(*) < 5 and count(*) > 2)
                or count(*) = 3 --decide if cell is alive

        )
    select x, y
    from game
    where n = 3
)
select string_agg(cell, ' ') game_of_life
from (
         select grid_x, grid_y, case when x is not null then 'X' else '.' end cell
         from conway c
                  right join (
             select x.n grid_x, y.n grid_y
             from (select generate_series(least(1, min(x)), greatest(1, max(x))) n from conway) x
                      join (select generate_series(least(1, min(y)), greatest(1, max(y))) n from conway) y on 1 = 1) grid
                             on grid.grid_x = c.x and grid.grid_y = c.y) board
group by grid_x
order by grid_x;

The only difference between conway.sql and prettyprint.sql is the output.
Conway.sql just returns the coordinates of the live cells.
Prettyprint formats the result of the query as a 20x20 grid of live and dead cells.

prettyprint.sql now takes a table of varchars (initial_state) as board state. 'X' is a live cell '.' a dead cell.
The result of the query is formatted likewise. I limited the number of generations to 100.
The core query is still the same as the one in conway.sql, the only difference is the format of the input and output.

oh and the event that spiked my interest in this gist again is that the query is part of the tests in DuckDB: https://github.com/cwida/duckdb/blob/master/test/sql/cte/game_of_life.test_slow