Software developer blog

SQL on steroids

Few weeks ago we held an internal Coderetreat at Emarsys for all programmers within the company. When the business intelligence team heard about it they started to joke around, that they should drop by and implement Conway's Game of Life in SQL. I'm not much of an SQL wizard, but I rose to the challenge. First I tried it in MySQL, since that's the dialect I have the most experience with:

CREATE TABLE generations (`id` INT UNSIGNED, `x` BIGINT, `y` BIGINT);
INSERT INTO generations VALUES (0, 0, -1), (0, 1, 0), (0, -1, 1), (0, 0, 1), (0, 1, 1);
 
CREATE TABLE offsets (`x` BIGINT, `y` BIGINT);
INSERT INTO offsets VALUES (-1,-1),(-1, 0),(-1,1),(0,-1),(0,0),(0,1),(1,-1),(1,0),(1,1);
 
INSERT INTO generations 
  SELECT g.id+1 id, g.x+o.x x, g.y+o.y y
    FROM generations g 
      INNER JOIN offsets o 
    WHERE g.id=(SELECT MAX(id) FROM generations)
    GROUP BY g.x+o.x, g.y+o.y
      HAVING (MAX(o.x=0 AND o.y=0) = 1 AND COUNT(1) - 1 BETWEEN 2 AND 3)
        OR (MAX(o.x=0 AND o.y=0) = 0 AND COUNT(1) = 3);

It works, and it's not even that complicated. First we join the last population with an offset table to generate the set of living cells and their neighbors. Grouping by the cell coordinates plus the offsets we can get all necessary information. The expression max(o.x=0 and o.y=0) is one exactly if the cell was alive in the previous generation, otherwise it's zero. Using that we can count the number of neighbors too: count(1)-max(o.x=0 and o.y=0). The having clause utilizes that knowledge to filter the grouped rows to contain only the cells in the next generation.

@ //