snakes_ladders.sql

CREATE TABLE board(id serial primary key, moves int[]);
INSERT INTO board(moves) values('{0,0,0}');
WITH current AS (SELECT generate_series(1,3) p_no, unnest(moves) "pos" FROM board order by id desc LIMIT 3),
     roll AS (SELECT generate_series(1,3) p_no, round(random()*2)+1 result),
     move AS (SELECT coalesce('{"9":"2","3":"6"}'::json->>(c.pos+r.result)::text, (c.pos+r.result)::text)::int "new_pos", c.pos "old_pos"
                    FROM current c JOIN roll r ON c.p_no = r.p_no),
     constrained_move AS (SELECT (CASE WHEN m.new_pos > 10 THEN m.old_pos
                                       WHEN m.new_pos < 10 THEN m.new_pos
                                       ELSE 10 END) "final_pos" FROM move m),
     rec AS (INSERT INTO board(moves) (SELECT array_agg(cm.final_pos) FROM constrained_move cm))
SELECT * FROM board;