initial public version

24 files changed,1938insertions(+),0deletions(-) A .gitignore A README.md A examples/01.txt A examples/02.txt A examples/03.txt A examples/05.txt A examples/06.txt A examples/07.txt A examples/09.txt A examples/10.txt A examples/12.txt A examples/15.txt A examples/21.txt A sol01.sql A sol02.sql A sol03.sql A sol05.sql A sol06.sql A sol07.sql A sol09.sql A sol10.sql A sol12.sql A sol15.sql A sol21.sql

A => .gitignore +2 -0

@@ 1,2 @@private/ input

A => README.md +7 -0

@@ 1,7 @@Solutions to advent of code 2021 in sqlite3 To execute a solution, make sure that all inputs are saved in `input/NN.txt`, where NN is the day (01 to 25), and run: ``` $ sqlite3 solNN.sql ```

A => examples/01.txt +10 -0

@@ 1,10 @@199 200 208 210 200 207 240 269 260 263

A => examples/02.txt +6 -0

@@ 1,6 @@forward 5 down 5 forward 8 up 3 down 8 forward 2

A => examples/03.txt +12 -0

@@ 1,12 @@00100 11110 10110 10111 10101 01111 00111 11100 10000 11001 00010 01010

A => examples/05.txt +10 -0

@@ 1,10 @@0,9 -> 5,9 8,0 -> 0,8 9,4 -> 3,4 2,2 -> 2,1 7,0 -> 7,4 6,4 -> 2,0 0,9 -> 2,9 3,4 -> 1,4 0,0 -> 8,8 5,5 -> 8,2 \ No newline at end of file

A => examples/06.txt +1 -0

@@ 1,1 @@3,4,3,1,2 \ No newline at end of file

A => examples/07.txt +1 -0

@@ 1,1 @@16,1,2,0,4,2,7,1,2,14 \ No newline at end of file

A => examples/09.txt +5 -0

@@ 1,5 @@2199943210 3987894921 9856789892 8767896789 9899965678

A => examples/10.txt +10 -0

@@ 1,10 @@[({(<(())[]>[[{[]{<()<>> [(()[<>])]({[<{<<[]>>( {([(<{}[<>[]}>{[]{[(<()> (((({<>}<{<{<>}{[]{[]{} [[<[([]))<([[{}[[()]]] [{[{({}]{}}([{[{{{}}([] {<[[]]>}<{[{[{[]{()[[[] [<(<(<(<{}))><([]([]() <{([([[(<>()){}]>(<<{{ <{([{{}}[<[[[<>{}]]]>[]] \ No newline at end of file

A => examples/12.txt +10 -0

@@ 1,10 @@dc-end HN-start start-kj dc-start dc-HN LN-dc HN-end kj-sa kj-HN kj-dc \ No newline at end of file

A => examples/15.txt +10 -0

@@ 1,10 @@1163751742 1381373672 2136511328 3694931569 7463417111 1319128137 1359912421 3125421639 1293138521 2311944581

A => examples/21.txt +2 -0

@@ 1,2 @@Player 1 starting position: 4 Player 2 starting position: 8

A => sol01.sql +45 -0

@@ 1,45 @@create table input1 ( depth integer ); .mode csv .import input/01.txt input1 -- Part 1 select count(*) from input1 as i where depth > ( select depth from input1 as j where j.rowid < i.rowid order by rowid desc limit 1 ) ; -- Part 2 with smoothed_depth as ( select rowid, ( select case when count(*) = 3 then sum(depth) else null end from input1 as j where j.rowid between i.rowid - 2 and i.rowid ) as depth from input1 as i ) select count(*) from smoothed_depth as i where i.depth > ( select depth from smoothed_depth as j where j.rowid < i.rowid order by rowid desc limit 1 ) ;

A => sol02.sql +52 -0

@@ 1,52 @@create table input2 ( line text ); .mode csv .import input/02.txt input2 create view movement (rowid, direction, length) as select rowid, substr(line, 0, instr(line, ' ')), cast (substr(line, instr(line, ' ') + 1) as integer) from input2 ; -- part 1 create view total_movement as select direction, sum(length) as length from movement group by direction ; select (select length from total_movement where direction = 'forward') * ( (select length from total_movement where direction = 'down') - (select length from total_movement where direction = 'up') ) ; -- part 2 create view aim (rowid, aim) as select rowid, ifnull((select sum(length) from movement where rowid <= m.rowid and direction = 'down'), 0) - ifnull((select sum(length) from movement where rowid <= m.rowid and direction = 'up'), 0) from movement as m ; with delta as ( select length as dx, aim * length as dy from aim join movement on aim.rowid = movement.rowid where movement.direction = "forward" ) select (select sum(dx) from delta) * (select sum(dy) from delta);

A => sol03.sql +127 -0

@@ 1,127 @@create table input ( line text ); .mode csv .import input/03.txt input create view bits (rowid, idx, val) as with recursive bits (rowid, bitidx, bitval) as ( select 0, 0, null union all select case when bitidx <= (select length(line) from input where rowid = bits.rowid) then rowid else rowid + 1 end, case when bitidx <= (select length(line) from input where rowid = bits.rowid) then bitidx + 1 else 1 end, ( select case when substr(line, bitidx, 1) == "" then null else substr(line, bitidx, 1) end from input where input.rowid = bits.rowid ) as bitval from bits where rowid <= (select max(rowid) from input) ) select rowid, bitidx - 1, cast(bitval as integer) from bits where bitval is not null; -- part 1 create view most_common_bit as select (select max(length(line)) from input) - idx as idx, ( select val from bits where idx = x.idx group by val order by count(*) limit 1 ) as bit from bits as x group by idx; select sum(mcb.bit << mcb.idx) * sum((1-mcb.bit) << mcb.idx) from most_common_bit as mcb; -- part 2 create temporary table oxy ( rowid integer primary key ); insert into oxy select rowid from input; delete from oxy where rowid in ( select rowid from bits where idx = 1 and val <> ( select val from bits where idx = 1 group by val order by count(*) desc, val desc limit 1 ) ) ; delete from oxy where rowid in ( select oxy.rowid from bits join oxy on bits.rowid = oxy.rowid where bits.idx = 2 and val <> ( select val from bits join oxy on bits.rowid = oxy.rowid where idx = 2 group by val order by count(*) desc, val desc limit 1 ) ) and 2 <= (select max(length(line)) from input) ; delete from oxy where rowid in ( select oxy.rowid from bits join oxy on bits.rowid = oxy.rowid where bits.idx = 3 and val <> ( select val from bits join oxy on bits.rowid = oxy.rowid where idx = 3 group by val order by count(*) desc, val desc limit 1 ) ) and 3 <= (select max(length(line)) from input) ; delete from oxy where rowid in ( select oxy.rowid from bits join oxy on bits.rowid = oxy.rowid where bits.idx = 4 and val <> ( select val from bits join oxy on bits.rowid = oxy.rowid where idx = 4 group by val order by count(*) desc, val desc limit 1 ) ) and 4 <= (select max(length(line)) from input) ; delete from oxy where rowid in ( select oxy.rowid from bits join oxy on bits.rowid = oxy.rowid where bits.idx = 5 and val <> ( select val from bits join oxy on bits.rowid = oxy.rowid where idx = 5 group by val order by count(*) desc, val desc limit 1 ) ) and 5 <= (select max(length(line)) from input) ; delete from oxy where rowid in ( select oxy.rowid from bits join oxy on bits.rowid = oxy.rowid where bits.idx = 6 and val <> ( select val from bits join oxy on bits.rowid = oxy.rowid where idx = 6 group by val order by count(*) desc, val desc limit 1 ) ) and 6 <= (select max(length(line)) from input) ; delete from oxy where rowid in ( select oxy.rowid from bits join oxy on bits.rowid = oxy.rowid where bits.idx = 7 and val <> ( select val from bits join oxy on bits.rowid = oxy.rowid where idx = 7 group by val order by count(*) desc, val desc limit 1 ) ) and 7 <= (select max(length(line)) from input) ; delete from oxy where rowid in ( select oxy.rowid from bits join oxy on bits.rowid = oxy.rowid where bits.idx = 8 and val <> ( select val from bits join oxy on bits.rowid = oxy.rowid where idx = 8 group by val order by count(*) desc, val desc limit 1 ) ) and 8 <= (select max(length(line)) from input) ; delete from oxy where rowid in ( select oxy.rowid from bits join oxy on bits.rowid = oxy.rowid where bits.idx = 9 and val <> ( select val from bits join oxy on bits.rowid = oxy.rowid where idx = 9 group by val order by count(*) desc, val desc limit 1 ) ) and 9 <= (select max(length(line)) from input) ; delete from oxy where rowid in ( select oxy.rowid from bits join oxy on bits.rowid = oxy.rowid where bits.idx = 10 and val <> ( select val from bits join oxy on bits.rowid = oxy.rowid where idx = 10 group by val order by count(*) desc, val desc limit 1 ) ) and 10 <= (select max(length(line)) from input) ; delete from oxy where rowid in ( select oxy.rowid from bits join oxy on bits.rowid = oxy.rowid where bits.idx = 11 and val <> ( select val from bits join oxy on bits.rowid = oxy.rowid where idx = 11 group by val order by count(*) desc, val desc limit 1 ) ) and 11 <= (select max(length(line)) from input) ; delete from oxy where rowid in ( select oxy.rowid from bits join oxy on bits.rowid = oxy.rowid where bits.idx = 12 and val <> ( select val from bits join oxy on bits.rowid = oxy.rowid where idx = 12 group by val order by count(*) desc, val desc limit 1 ) ) and 12 <= (select max(length(line)) from input) ; create temporary view oxygen_rating (o) as select sum(bits.val << ((select max(length(line)) from input) - bits.idx)) from oxy join bits on bits.rowid = oxy.rowid; create temporary table co2 ( rowid integer primary key ); insert into co2 select rowid from input; delete from co2 where rowid in ( select co2.rowid from bits join co2 on bits.rowid = co2.rowid where bits.idx = 1 and val <> ( select val from bits join co2 on bits.rowid = co2.rowid where idx = 1 group by val order by count(*) asc, val asc limit 1 ) ) and 1 <= (select max(length(line)) from input) ; delete from co2 where rowid in ( select co2.rowid from bits join co2 on bits.rowid = co2.rowid where bits.idx = 2 and val <> ( select val from bits join co2 on bits.rowid = co2.rowid where idx = 2 group by val order by count(*) asc, val asc limit 1 ) ) and 2 <= (select max(length(line)) from input) ; delete from co2 where rowid in ( select co2.rowid from bits join co2 on bits.rowid = co2.rowid where bits.idx = 3 and val <> ( select val from bits join co2 on bits.rowid = co2.rowid where idx = 3 group by val order by count(*) asc, val asc limit 1 ) ) and 3 <= (select max(length(line)) from input) ; delete from co2 where rowid in ( select co2.rowid from bits join co2 on bits.rowid = co2.rowid where bits.idx = 4 and val <> ( select val from bits join co2 on bits.rowid = co2.rowid where idx = 4 group by val order by count(*) asc, val asc limit 1 ) ) and 4 <= (select max(length(line)) from input) ; delete from co2 where rowid in ( select co2.rowid from bits join co2 on bits.rowid = co2.rowid where bits.idx = 5 and val <> ( select val from bits join co2 on bits.rowid = co2.rowid where idx = 5 group by val order by count(*) asc, val asc limit 1 ) ) and 5 <= (select max(length(line)) from input) ; delete from co2 where rowid in ( select co2.rowid from bits join co2 on bits.rowid = co2.rowid where bits.idx = 6 and val <> ( select val from bits join co2 on bits.rowid = co2.rowid where idx = 6 group by val order by count(*) asc, val asc limit 1 ) ) and 6 <= (select max(length(line)) from input) ; delete from co2 where rowid in ( select co2.rowid from bits join co2 on bits.rowid = co2.rowid where bits.idx = 7 and val <> ( select val from bits join co2 on bits.rowid = co2.rowid where idx = 7 group by val order by count(*) asc, val asc limit 1 ) ) and 7 <= (select max(length(line)) from input) ; delete from co2 where rowid in ( select co2.rowid from bits join co2 on bits.rowid = co2.rowid where bits.idx = 8 and val <> ( select val from bits join co2 on bits.rowid = co2.rowid where idx = 8 group by val order by count(*) asc, val asc limit 1 ) ) and 8 <= (select max(length(line)) from input) ; delete from co2 where rowid in ( select co2.rowid from bits join co2 on bits.rowid = co2.rowid where bits.idx = 9 and val <> ( select val from bits join co2 on bits.rowid = co2.rowid where idx = 9 group by val order by count(*) asc, val asc limit 1 ) ) and 9 <= (select max(length(line)) from input) ; delete from co2 where rowid in ( select co2.rowid from bits join co2 on bits.rowid = co2.rowid where bits.idx = 10 and val <> ( select val from bits join co2 on bits.rowid = co2.rowid where idx = 10 group by val order by count(*) asc, val asc limit 1 ) ) and 10 <= (select max(length(line)) from input) ; delete from co2 where rowid in ( select co2.rowid from bits join co2 on bits.rowid = co2.rowid where bits.idx = 11 and val <> ( select val from bits join co2 on bits.rowid = co2.rowid where idx = 11 group by val order by count(*) asc, val asc limit 1 ) ) and 11 <= (select max(length(line)) from input) ; delete from co2 where rowid in ( select co2.rowid from bits join co2 on bits.rowid = co2.rowid where bits.idx = 12 and val <> ( select val from bits join co2 on bits.rowid = co2.rowid where idx = 12 group by val order by count(*) asc, val asc limit 1 ) ) and 12 <= (select max(length(line)) from input) ; create temporary view co2_rating (c) as select sum(bits.val << ((select max(length(line)) from input) - bits.idx)) from co2 join bits on bits.rowid = co2.rowid; select o * c from oxygen_rating, co2_rating;

A => sol05.sql +69 -0

@@ 1,69 @@create table input ( a text, b text, c text ); .mode csv .import input/05.txt input create temporary table vents ( x1 integer, y1 integer, x2 integer, y2 integer ); insert into vents select cast (a as integer), cast (substr(b, 1, instr(b, ' -> ')) as integer), cast (substr(b, instr(b, ' -> ') + 4) as integer), cast (c as integer) from input; -- part 1 with recursive f (x1, y1, x2, y2) as ( select case when x1 < x2 or y1 < y2 then x1 else x2 end, case when x1 < x2 or y1 < y2 then y1 else y2 end, case when x1 < x2 or y1 < y2 then x2 else x1 end, case when x1 < x2 or y1 < y2 then y2 else y1 end from vents where x1 = x2 or y1 = y2 union all select case when x1 < x2 then x1 + 1 else x1 end, case when y1 < y2 then y1 + 1 else y1 end, x2, y2 from f where x1 < x2 or y1 < y2 ) select count(*) from (select * from f group by x1, y1 having count(*) > 1); -- part 2 with recursive f (x1, y1, x2, y2) as ( select x1, y1, x2, y2 from vents union all select case when x1 < x2 then x1 + 1 when x1 > x2 then x1 - 1 else x1 end, case when y1 < y2 then y1 + 1 when y1 > y2 then y1 - 1 else y1 end, x2, y2 from f where x1 <> x2 or y1 <> y2 ) select count(*) from (select * from f group by x1, y1 having count(*) > 1);

A => sol06.sql +92 -0

@@ 1,92 @@create table input ( line text ); .mode line .import input/06.txt input .mode csv create temporary table timers ( time integer ); with recursive f (k, time, rest) as ( select 0, 0, line from input union select k + 1, case when instr(rest, ',') then substr(rest, 1, instr(rest, ',') - 1) else rest end, case when instr(rest, ',') then substr(rest, instr(rest, ',') + 1) else '' end from f where rest <> '' ) insert into timers select cast (time as integer) from f where k > 0 ; -- part 1 create temporary table step ( t1 integer, t2 integer ); insert into step (t1, t2) values -- reached 0 -> spawn a new one for the next turn (0, 6), (0, 8), -- otherwise just decrease the timer by one (1, 0), (2, 1), (3, 2), (4, 3), (5, 4), (6, 5), (7, 6), (8, 7) ; with recursive f (step, time) as ( select 0, time from timers union all select step + 1, t2 from f join step on f.time = step.t1 where step < 80 ) select count(*) from f where step = 80; -- part 2 with recursive f (step, t0, t1, t2, t3, t4, t5, t6, t7, t8) as ( select 0, (select count(*) from timers where time = 0), (select count(*) from timers where time = 1), (select count(*) from timers where time = 2), (select count(*) from timers where time = 3), (select count(*) from timers where time = 4), (select count(*) from timers where time = 5), (select count(*) from timers where time = 6), (select count(*) from timers where time = 7), (select count(*) from timers where time = 8) union select step + 1, t1, t2, t3, t4, t5, t6, t7 + t0, t8, t0 from f where step < 256 ) select t0+t1+t2+t3+t4+t5+t6+t7+t8 from f where step = 256;

A => sol07.sql +55 -0

@@ 1,55 @@create table input ( line text ); .mode list .import input/07.txt input create temporary table height ( rowid integer primary key, height integer ); with recursive f (rowid, height, rest) as ( select 0, null, line from input union all select rowid + 1, case when instr(rest, ',') then substr(rest, 1, instr(rest, ',') - 1) else rest end, case when instr(rest, ',') then substr(rest, instr(rest, ',') + 1) else '' end from f where rest <> '' ) insert into height (rowid, height) select rowid, cast (height as integer) from f where height is not null ; -- part 1 select ( select sum(abs(h2.height - h1.height)) from height as h2 ) as fuel from height as h1 order by fuel limit 1; -- part 2 with recursive h1 ( height ) as ( select (select min(height) from height) union select height + 1 from h1 where height < (select max(height) from height) ) select ( select sum(abs(h2.height - h1.height) * (abs(h2.height - h1.height) + 1) / 2) from height as h2 ) as fuel from h1 order by fuel limit 1;

A => sol09.sql +120 -0

@@ 1,120 @@create table input ( line text ); .mode csv .import input/09.txt input create temporary table map ( x integer, y integer, z integer, primary key (x, y) ); with recursive f (x, y, z, rest) as ( select 0, rowid, null, line from input union all select x + 1, y, cast(substr(rest, 1, 1) as integer), substr(rest, 2) from f where x < (select max(length(line)) from input) ) insert into map (x, y, z) select x, y, z from f where z is not null ; -- part 1 create temporary view high_points as select x, y, z from map as m where z < ( select min(z) from map where abs(x - m.x) + abs(y - m.y) = 1 ) ; select sum(z + 1) from high_points; -- part 2 create temporary table basins ( x integer, y integer, basin integer, primary key (x, y) ); insert into basins (x, y, basin) select x, y, rowid from map where z < 9; update basins as b set basin = ( select min(basin) from basins join map on basins.x = map.x and basins.y = map.y where (map.x = b.x and map.y = b.y + 1) or (map.x = b.x and map.y = b.y - 1) or (map.x = b.x + 1 and map.y = b.y) or (map.x = b.x - 1 and map.y = b.y) ); update basins as b set basin = ( select min(basin) from basins join map on basins.x = map.x and basins.y = map.y where (map.x = b.x and map.y = b.y + 1) or (map.x = b.x and map.y = b.y - 1) or (map.x = b.x + 1 and map.y = b.y) or (map.x = b.x - 1 and map.y = b.y) ); update basins as b set basin = ( select min(basin) from basins join map on basins.x = map.x and basins.y = map.y where (map.x = b.x and map.y = b.y + 1) or (map.x = b.x and map.y = b.y - 1) or (map.x = b.x + 1 and map.y = b.y) or (map.x = b.x - 1 and map.y = b.y) ); update basins as b set basin = ( select min(basin) from basins join map on basins.x = map.x and basins.y = map.y where (map.x = b.x and map.y = b.y + 1) or (map.x = b.x and map.y = b.y - 1) or (map.x = b.x + 1 and map.y = b.y) or (map.x = b.x - 1 and map.y = b.y) ); update basins as b set basin = ( select min(basin) from basins join map on basins.x = map.x and basins.y = map.y where (map.x = b.x and map.y = b.y + 1) or (map.x = b.x and map.y = b.y - 1) or (map.x = b.x + 1 and map.y = b.y) or (map.x = b.x - 1 and map.y = b.y) ); update basins as b set basin = ( select min(basin) from basins join map on basins.x = map.x and basins.y = map.y where (map.x = b.x and map.y = b.y + 1) or (map.x = b.x and map.y = b.y - 1) or (map.x = b.x + 1 and map.y = b.y) or (map.x = b.x - 1 and map.y = b.y) ); update basins as b set basin = ( select min(basin) from basins join map on basins.x = map.x and basins.y = map.y where (map.x = b.x and map.y = b.y + 1) or (map.x = b.x and map.y = b.y - 1) or (map.x = b.x + 1 and map.y = b.y) or (map.x = b.x - 1 and map.y = b.y) ); update basins as b set basin = ( select min(basin) from basins join map on basins.x = map.x and basins.y = map.y where (map.x = b.x and map.y = b.y + 1) or (map.x = b.x and map.y = b.y - 1) or (map.x = b.x + 1 and map.y = b.y) or (map.x = b.x - 1 and map.y = b.y) ); update basins as b set basin = ( select min(basin) from basins join map on basins.x = map.x and basins.y = map.y where (map.x = b.x and map.y = b.y + 1) or (map.x = b.x and map.y = b.y - 1) or (map.x = b.x + 1 and map.y = b.y) or (map.x = b.x - 1 and map.y = b.y) ); update basins as b set basin = ( select min(basin) from basins join map on basins.x = map.x and basins.y = map.y where (map.x = b.x and map.y = b.y + 1) or (map.x = b.x and map.y = b.y - 1) or (map.x = b.x + 1 and map.y = b.y) or (map.x = b.x - 1 and map.y = b.y) ); update basins as b set basin = ( select min(basin) from basins join map on basins.x = map.x and basins.y = map.y where (map.x = b.x and map.y = b.y + 1) or (map.x = b.x and map.y = b.y - 1) or (map.x = b.x + 1 and map.y = b.y) or (map.x = b.x - 1 and map.y = b.y) ); update basins as b set basin = ( select min(basin) from basins join map on basins.x = map.x and basins.y = map.y where (map.x = b.x and map.y = b.y + 1) or (map.x = b.x and map.y = b.y - 1) or (map.x = b.x + 1 and map.y = b.y) or (map.x = b.x - 1 and map.y = b.y) ); update basins as b set basin = ( select min(basin) from basins join map on basins.x = map.x and basins.y = map.y where (map.x = b.x and map.y = b.y + 1) or (map.x = b.x and map.y = b.y - 1) or (map.x = b.x + 1 and map.y = b.y) or (map.x = b.x - 1 and map.y = b.y) ); update basins as b set basin = ( select min(basin) from basins join map on basins.x = map.x and basins.y = map.y where (map.x = b.x and map.y = b.y + 1) or (map.x = b.x and map.y = b.y - 1) or (map.x = b.x + 1 and map.y = b.y) or (map.x = b.x - 1 and map.y = b.y) ); update basins as b set basin = ( select min(basin) from basins join map on basins.x = map.x and basins.y = map.y where (map.x = b.x and map.y = b.y + 1) or (map.x = b.x and map.y = b.y - 1) or (map.x = b.x + 1 and map.y = b.y) or (map.x = b.x - 1 and map.y = b.y) ); update basins as b set basin = ( select min(basin) from basins join map on basins.x = map.x and basins.y = map.y where (map.x = b.x and map.y = b.y + 1) or (map.x = b.x and map.y = b.y - 1) or (map.x = b.x + 1 and map.y = b.y) or (map.x = b.x - 1 and map.y = b.y) ); update basins as b set basin = ( select min(basin) from basins join map on basins.x = map.x and basins.y = map.y where (map.x = b.x and map.y = b.y + 1) or (map.x = b.x and map.y = b.y - 1) or (map.x = b.x + 1 and map.y = b.y) or (map.x = b.x - 1 and map.y = b.y) ); update basins as b set basin = ( select min(basin) from basins join map on basins.x = map.x and basins.y = map.y where (map.x = b.x and map.y = b.y + 1) or (map.x = b.x and map.y = b.y - 1) or (map.x = b.x + 1 and map.y = b.y) or (map.x = b.x - 1 and map.y = b.y) ); update basins as b set basin = ( select min(basin) from basins join map on basins.x = map.x and basins.y = map.y where (map.x = b.x and map.y = b.y + 1) or (map.x = b.x and map.y = b.y - 1) or (map.x = b.x + 1 and map.y = b.y) or (map.x = b.x - 1 and map.y = b.y) ); update basins as b set basin = ( select min(basin) from basins join map on basins.x = map.x and basins.y = map.y where (map.x = b.x and map.y = b.y + 1) or (map.x = b.x and map.y = b.y - 1) or (map.x = b.x + 1 and map.y = b.y) or (map.x = b.x - 1 and map.y = b.y) ); update basins as b set basin = ( select min(basin) from basins join map on basins.x = map.x and basins.y = map.y where (map.x = b.x and map.y = b.y + 1) or (map.x = b.x and map.y = b.y - 1) or (map.x = b.x + 1 and map.y = b.y) or (map.x = b.x - 1 and map.y = b.y) ); update basins as b set basin = ( select min(basin) from basins join map on basins.x = map.x and basins.y = map.y where (map.x = b.x and map.y = b.y + 1) or (map.x = b.x and map.y = b.y - 1) or (map.x = b.x + 1 and map.y = b.y) or (map.x = b.x - 1 and map.y = b.y) ); update basins as b set basin = ( select min(basin) from basins join map on basins.x = map.x and basins.y = map.y where (map.x = b.x and map.y = b.y + 1) or (map.x = b.x and map.y = b.y - 1) or (map.x = b.x + 1 and map.y = b.y) or (map.x = b.x - 1 and map.y = b.y) ); update basins as b set basin = ( select min(basin) from basins join map on basins.x = map.x and basins.y = map.y where (map.x = b.x and map.y = b.y + 1) or (map.x = b.x and map.y = b.y - 1) or (map.x = b.x + 1 and map.y = b.y) or (map.x = b.x - 1 and map.y = b.y) ); update basins as b set basin = ( select min(basin) from basins join map on basins.x = map.x and basins.y = map.y where (map.x = b.x and map.y = b.y + 1) or (map.x = b.x and map.y = b.y - 1) or (map.x = b.x + 1 and map.y = b.y) or (map.x = b.x - 1 and map.y = b.y) ); update basins as b set basin = ( select min(basin) from basins join map on basins.x = map.x and basins.y = map.y where (map.x = b.x and map.y = b.y + 1) or (map.x = b.x and map.y = b.y - 1) or (map.x = b.x + 1 and map.y = b.y) or (map.x = b.x - 1 and map.y = b.y) ); create temporary table sizes ( rowid integer primary key, size integer ); insert into sizes (size) select count(*) as size from basins group by basin order by size desc limit 3 ; -- why does sql not have a PRODUCT aggregation? -- or, for that matter, a general filter? with recursive f (n, prod) as ( select 1, 1 union all select n + 1, prod * coalesce((select size from sizes where rowid = n), 1) from f where n <= (select max(rowid) from sizes) ) select max(prod) from f;

A => sol10.sql +718 -0

@@ 1,718 @@create table input ( line text ); .mode csv .import input/10.txt input create temporary table chars ( line integer, n integer, symbol char, primary key (n, line) ); with recursive f (line, n, symbol, rest) as ( select rowid, 0, null, line from input union all select line, n + 1, case when substr(rest, 1, 1) <> '' then cast(substr(rest, 1, 1) as char) else null end, substr(rest, 2) from f where n <= (select max(length(line)) from input) ) insert into chars (line, n, symbol) select line, n, symbol from f where symbol is not null ; create temporary table scores ( symbol char primary key, score integer ); insert into scores values (')', 3), (']', 57), ('}', 1197), ('>', 25137) ; create temporary table pairs ( open char unique, close char unique ); insert into pairs values ('(', ')'), ('[', ']'), ('{', '}'), ('<', '>') ; -- part 1 create temporary table stack ( line integer, [order] integer, expected integer, primary key (line, [order]) ); create temporary table step ( step integer primary key ); -- STEP 1 delete from step; insert into step (step) values (1); -- begin: for all lines that contain a starting symbol at the first position, push the corresponding end symbol on the stack insert into stack select line, 1, close from chars join step on chars.n = step join pairs on chars.symbol = pairs.open ; -- STEP 2 delete from step; insert into step (step) values (2); -- for all lines that have a starting symbol on position 2, push the corresponding end symbol on the stack insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; -- for all lines that have an end symbol on position 2 that doesn't match the one on the stack, insert a new null to mark the location insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; -- for all lines that have an end symbol on position 2 that does match the one on the stack, remove that symbol from the stack delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; -- now do it 100 more times ðŸ™ƒ delete from step; insert into step (step) values (3); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (4); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (5); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (6); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (7); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (8); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (9); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (10); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (11); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (12); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (13); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (14); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (15); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (16); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (17); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (18); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (19); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (20); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (21); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (22); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (23); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (24); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (25); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (26); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (27); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (28); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (29); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (30); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (31); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (32); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (33); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (34); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (35); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (36); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (37); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (38); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (39); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (40); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (41); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (42); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (43); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (44); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (45); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (46); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (47); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (48); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (49); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (50); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (51); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (52); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (53); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (54); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (55); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (56); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (57); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (58); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (59); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (60); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (61); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (62); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (63); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (64); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (65); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (66); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (67); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (68); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (69); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (70); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (71); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (72); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (73); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (74); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (75); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (76); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (77); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (78); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (79); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (80); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (81); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (82); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (83); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (84); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (85); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (86); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (87); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (88); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (89); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (90); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (91); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (92); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (93); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (94); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (95); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (96); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (97); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (98); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (99); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (100); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (101); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (102); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (103); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (104); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (105); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (106); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (107); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (108); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (109); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; delete from step; insert into step (step) values (110); insert into stack select line, coalesce((select max([order]) from stack where stack.line = chars.line) + 1, 1), close from chars join pairs on chars.symbol = pairs.open join step on n = step ; insert into stack (line, [order], expected) select chars.line, (select step from step), null from chars left join stack on chars.line = stack.line join step on n = step and coalesce(stack.line in (select line from chars join pairs on symbol = close join step on n = step), true) and coalesce(stack.[order] = (select max([order]) from stack as s where s.line = stack.line), true) and coalesce(chars.symbol <> stack.expected, true) ; delete from stack where (line, [order]) in ( select stack.line as line, [order] from chars join stack on chars.line = stack.line join step on n = step and stack.line in (select line from chars join pairs on symbol = close join step on n = step) and stack.[order] = (select max([order]) from stack as s where s.line = stack.line) and chars.symbol = stack.expected ) ; select sum(score) from chars join ( select line, min([order]) as position from stack where expected is null group by line ) as s on s.line = chars.line and chars.n = position join scores on scores.symbol = chars.symbol ; -- part 2 -- discard the incomplete lines delete from chars where chars.line in ( select line from stack where stack.expected is null ) ; delete from scores; insert into scores values (')', 1), (']', 2), ('}', 3), ('>', 4) ; with recursive f (line, depth, score) as ( select chars.line, max([order]), 0 from chars join stack on chars.line = stack.line group by chars.line union select f.line, depth - 1, 5 * score + (select score from scores as s where s.symbol = stack.expected) from f join stack on stack.line = f.line where stack.[order] = depth and depth > 0 ) select score from f where depth = 0 group by line order by score limit 1 offset (select (count(distinct line) - 1) / 2 from f);

A => sol12.sql +61 -0

@@ 1,61 @@create table input ( line text ); .mode csv .import input/12.txt input pragma case_sensitive_like = true; create temporary table caves ( [from] integer, [to] integer ); insert into caves select substr(line, 1, instr(line, '-') - 1), substr(line, instr(line, '-') + 1) from input; insert into caves select substr(line, instr(line, '-') + 1), substr(line, 1, instr(line, '-') - 1) from input; -- part 1 with recursive f (path, current) as ( select 'start', 'start' union all select path || ',' || [to], [to] from f join caves on current = [from] where current <> 'end' and not ([to] = lower([to]) and instr(path, [to]) > 0) ) select count(path) from f where current = 'end'; -- part 2 with recursive f (path, current, visited) as ( select 'start', 'start', null union all select path || ',' || [to], [to], case when [to] = lower([to]) and instr(path, [to]) > 0 then [to] else visited end from f join caves on current = [from] where current <> 'end' and [to] <> 'start' and ( visited is null or [to] <> lower([to]) or instr(path, [to]) = 0 ) ) select count(path) from f where current = 'end';

A => sol15.sql +322 -0

@@ 1,322 @@create table input ( line text ); .mode csv .import input/15.txt input create temporary table map ( x integer, y integer, risk integer, primary key (x, y) ); with recursive f (x, y, risk, rest) as ( select 0, rowid, null, line from input union all select x + 1, y, cast(substr(rest, 1, 1) as integer), substr(rest, 2) from f where x < (select max(length(line)) from input) ) insert into map (x, y, risk) select x, y, risk from f where risk is not null ; -- part 1 create temporary table step ( dx integer, dy integer ); insert into step values (1, 0), (0, 1) ; /* -- works in theory, but it's too inefficient: with recursive f (x, y, total_risk, len) as ( select 1, 1, 0, 0 union all select f.x + dx, f.y + dy, total_risk + risk, len + 1 from f, step join map on map.x = f.x + dx and map.y = f.y + dy ) select total_risk from f where x = (select max(x) from map) and y = (select max(y) from map); */ create temporary table min_risk ( x integer, y integer, total_risk, primary key (x, y) ); create index min_risk_index1 on min_risk (total_risk); create index min_risk_index2 on min_risk (x + y); --create index map_index1 on map (x + y); insert into min_risk select x, y, null from map; update min_risk set total_risk = 0 where x = 1 and y = 1; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 2 group by map.x, map.y having count(*) > 0 ; -- now 200 more times insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 3 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 4 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 5 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 6 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 7 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 8 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 9 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 10 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 11 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 12 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 13 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 14 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 15 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 16 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 17 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 18 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 19 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 20 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 21 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 22 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 23 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 24 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 25 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 26 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 27 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 28 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 29 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 30 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 31 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 32 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 33 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 34 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 35 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 36 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 37 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 38 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 39 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 40 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 41 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 42 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 43 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 44 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 45 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 46 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 47 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 48 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 49 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 50 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 51 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 52 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 53 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 54 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 55 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 56 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 57 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 58 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 59 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 60 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 61 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 62 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 63 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 64 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 65 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 66 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 67 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 68 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 69 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 70 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 71 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 72 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 73 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 74 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 75 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 76 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 77 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 78 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 79 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 80 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 81 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 82 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 83 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 84 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 85 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 86 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 87 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 88 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 89 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 90 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 91 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 92 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 93 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 94 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 95 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 96 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 97 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 98 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 99 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 100 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 101 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 102 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 103 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 104 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 105 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 106 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 107 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 108 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 109 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 110 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 111 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 112 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 113 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 114 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 115 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 116 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 117 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 118 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 119 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 120 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 121 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 122 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 123 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 124 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 125 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 126 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 127 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 128 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 129 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 130 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 131 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 132 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 133 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 134 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 135 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 136 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 137 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 138 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 139 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 140 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 141 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 142 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 143 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 144 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 145 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 146 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 147 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 148 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 149 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 150 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 151 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 152 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 153 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 154 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 155 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 156 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 157 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 158 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 159 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 160 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 161 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 162 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 163 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 164 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 165 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 166 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 167 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 168 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 169 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 170 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 171 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 172 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 173 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 174 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 175 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 176 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 177 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 178 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 179 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 180 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 181 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 182 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 183 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 184 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 185 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 186 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 187 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 188 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 189 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 190 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 191 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 192 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 193 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 194 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 195 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 196 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 197 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 198 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 199 group by map.x, map.y having count(*) > 0 ; insert or replace into min_risk select map.x, map.y, min(r.total_risk) + risk from map, step join min_risk as r on map.x = r.x + dx and map.y = r.y + dy where r.x + r.y = 200 group by map.x, map.y having count(*) > 0 ; select total_risk from min_risk where x = (select max(x) from map) and y = (select max(y) from map); -- part 2 with recursive f (n, tx, ty) as ( select 0, 1, 1 union select n + 1, (n % 5) + 1, (n / 5) + 1 from f where n < 25 ) insert into map (x, y, risk) select x + (tx - 1) * (select max(x) from map), y + (ty - 1) * (select max(y) from map), ((risk + tx + ty - 3) % 9) + 1 from map, f where tx + ty > 2; delete from min_risk; insert into min_risk select x, y, null from map; update min_risk set total_risk = 0 where x = 1 and y = 1; -- dynamic programming doesn't work anymore for this part -- :(

A => sol21.sql +191 -0

@@ 1,191 @@create table input ( line text ); .mode csv .import input/21.txt input -- part 1 with recursive f (turn, who, pos1, pos2, score1, score2) as ( select 0, 1, (select cast (substr(line, 28) as integer) from input where rowid = 1), (select cast (substr(line, 28) as integer) from input where rowid = 2), 0, 0 union all select turn + 3, who % 2 + 1, case when who = 1 then (pos1 - 1 + (turn % 1000 + 1) + ((turn + 1) % 1000 + 1) + ((turn + 2) % 1000 + 1)) % 10 + 1 else pos1 end, case when who = 2 then (pos2 - 1 + (turn % 1000 + 1) + ((turn + 1) % 1000 + 1) + ((turn + 2) % 1000 + 1)) % 10 + 1 else pos2 end, case when who = 1 then score1 + (pos1 - 1 + (turn % 1000 + 1) + ((turn + 1) % 1000 + 1) + ((turn + 2) % 1000 + 1)) % 10 + 1 else score1 end, case when who = 2 then score2 + (pos2 - 1 + (turn % 1000 + 1) + ((turn + 1) % 1000 + 1) + ((turn + 2) % 1000 + 1)) % 10 + 1 else score2 end from f where score1 < 1000 and score2 < 1000 ) select case when who = 2 then score2 * turn else score1 * turn end from f where turn = (select max(turn) from f) ; -- part 2 create temporary table dice ( value integer, combinations integer ); with recursive f (n) as ( select 1 union select n + 1 from f where n < 3 ) insert into dice select d1.n + d2.n + d3.n as total, count(*) from f as d1, f as d2, f as d3 group by total; /* with recursive f (who, p1, p2, score1, score2, universes) as ( select 1, (select cast (substr(line, 28) as integer) from input where rowid = 1), (select cast (substr(line, 28) as integer) from input where rowid = 2), 0, 0, 1 union select who % 2 + 1, case when who = 1 then ((p1 + value - 1) % 10) + 1 else p1 end, case when who = 2 then ((p2 + value - 1) % 10) + 1 else p2 end, case when who = 1 then ((p1 + value - 1) % 10) + 1 else 0 end + score1, case when who = 2 then ((p2 + value - 1) % 10) + 1 else 0 end + score2, universes * combinations from f, dice where score1 < 21 and score2 < 21 -- we'd have to fix the turn here as well to ensure we don't overcount ) select sum(universes) from f where score1 >= 10 or score2 >= 10 group by who order by sum(universes) desc limit 1; */ create table gamestate ( turn integer, pos1 integer, pos2 integer, score1 integer, score2 integer, universes integer ); insert into gamestate values ( 1, (select cast (substr(line, 28) as integer) from input where rowid = 1), (select cast (substr(line, 28) as integer) from input where rowid = 2), 0, 0, 1 ) ; -- player 1 turn; throw dice, increment timer, count universes -- finally, merge identical states insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, (pos1 - 1 + value) % 10 + 1 as p1, pos2 as p2, score1 + (pos1 - 1 + value) % 10 + 1 as s1, score2 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; -- player 2 turn insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, pos1 as p1, (pos2 - 1 + value) % 10 + 1 as p2, score1 as s1, score2 + (pos2 - 1 + value) % 10 + 1 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; -- a few more full rounds (player 1 & player 2) ought to be enough insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, (pos1 - 1 + value) % 10 + 1 as p1, pos2 as p2, score1 + (pos1 - 1 + value) % 10 + 1 as s1, score2 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, pos1 as p1, (pos2 - 1 + value) % 10 + 1 as p2, score1 as s1, score2 + (pos2 - 1 + value) % 10 + 1 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, (pos1 - 1 + value) % 10 + 1 as p1, pos2 as p2, score1 + (pos1 - 1 + value) % 10 + 1 as s1, score2 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, pos1 as p1, (pos2 - 1 + value) % 10 + 1 as p2, score1 as s1, score2 + (pos2 - 1 + value) % 10 + 1 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, (pos1 - 1 + value) % 10 + 1 as p1, pos2 as p2, score1 + (pos1 - 1 + value) % 10 + 1 as s1, score2 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, pos1 as p1, (pos2 - 1 + value) % 10 + 1 as p2, score1 as s1, score2 + (pos2 - 1 + value) % 10 + 1 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, (pos1 - 1 + value) % 10 + 1 as p1, pos2 as p2, score1 + (pos1 - 1 + value) % 10 + 1 as s1, score2 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, pos1 as p1, (pos2 - 1 + value) % 10 + 1 as p2, score1 as s1, score2 + (pos2 - 1 + value) % 10 + 1 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, (pos1 - 1 + value) % 10 + 1 as p1, pos2 as p2, score1 + (pos1 - 1 + value) % 10 + 1 as s1, score2 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, pos1 as p1, (pos2 - 1 + value) % 10 + 1 as p2, score1 as s1, score2 + (pos2 - 1 + value) % 10 + 1 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, (pos1 - 1 + value) % 10 + 1 as p1, pos2 as p2, score1 + (pos1 - 1 + value) % 10 + 1 as s1, score2 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, pos1 as p1, (pos2 - 1 + value) % 10 + 1 as p2, score1 as s1, score2 + (pos2 - 1 + value) % 10 + 1 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, (pos1 - 1 + value) % 10 + 1 as p1, pos2 as p2, score1 + (pos1 - 1 + value) % 10 + 1 as s1, score2 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, pos1 as p1, (pos2 - 1 + value) % 10 + 1 as p2, score1 as s1, score2 + (pos2 - 1 + value) % 10 + 1 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, (pos1 - 1 + value) % 10 + 1 as p1, pos2 as p2, score1 + (pos1 - 1 + value) % 10 + 1 as s1, score2 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, pos1 as p1, (pos2 - 1 + value) % 10 + 1 as p2, score1 as s1, score2 + (pos2 - 1 + value) % 10 + 1 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, (pos1 - 1 + value) % 10 + 1 as p1, pos2 as p2, score1 + (pos1 - 1 + value) % 10 + 1 as s1, score2 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, pos1 as p1, (pos2 - 1 + value) % 10 + 1 as p2, score1 as s1, score2 + (pos2 - 1 + value) % 10 + 1 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, (pos1 - 1 + value) % 10 + 1 as p1, pos2 as p2, score1 + (pos1 - 1 + value) % 10 + 1 as s1, score2 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, pos1 as p1, (pos2 - 1 + value) % 10 + 1 as p2, score1 as s1, score2 + (pos2 - 1 + value) % 10 + 1 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, (pos1 - 1 + value) % 10 + 1 as p1, pos2 as p2, score1 + (pos1 - 1 + value) % 10 + 1 as s1, score2 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, pos1 as p1, (pos2 - 1 + value) % 10 + 1 as p2, score1 as s1, score2 + (pos2 - 1 + value) % 10 + 1 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, (pos1 - 1 + value) % 10 + 1 as p1, pos2 as p2, score1 + (pos1 - 1 + value) % 10 + 1 as s1, score2 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, pos1 as p1, (pos2 - 1 + value) % 10 + 1 as p2, score1 as s1, score2 + (pos2 - 1 + value) % 10 + 1 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, (pos1 - 1 + value) % 10 + 1 as p1, pos2 as p2, score1 + (pos1 - 1 + value) % 10 + 1 as s1, score2 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, pos1 as p1, (pos2 - 1 + value) % 10 + 1 as p2, score1 as s1, score2 + (pos2 - 1 + value) % 10 + 1 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, (pos1 - 1 + value) % 10 + 1 as p1, pos2 as p2, score1 + (pos1 - 1 + value) % 10 + 1 as s1, score2 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; insert into gamestate select t, p1, p2, s1, s2, sum(u) from ( select (select max(turn) + 1 from gamestate) as t, pos1 as p1, (pos2 - 1 + value) % 10 + 1 as p2, score1 as s1, score2 + (pos2 - 1 + value) % 10 + 1 as s2, universes * combinations as u from gamestate, dice where turn = (select max(turn) from gamestate) and score1 < 21 and score2 < 21 ) group by t, p1, p2, s1, s2 ; delete from gamestate where turn < (select max(turn) from gamestate) and score1 < 21 and score2 < 21; select sum(universes) as wins from gamestate where score1 >= 21 or score2 >= 21 group by turn % 2 order by wins desc limit 1;