day09.sql

CREATE TABLE day09 (
    direction ENUM('U', 'D', 'L', 'R'), 
    distance int
) ENGINE=BLACKHOLE;

CREATE TABLE knots (
    id int primary key,
    x int,
    y int
);

INSERT INTO knots (id, x, y)
WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 10)
SELECT n, 0 AS x, 0 AS y FROM cte;

CREATE TABLE knotRecord (
    knotId int,
    x int,
    y int
);

INSERT INTO knotRecord SELECT * FROM knots WHERE id IN (2, 10);

DELIMITER $
CREATE TRIGGER knots_ai AFTER UPDATE ON knots FOR EACH ROW 
BEGIN
    IF NEW.id IN (2, 10) AND (NEW.x != OLD.x OR NEW.y != OLD.y) THEN
        INSERT INTO knotRecord VALUES (NEW.id, NEW.x, NEW.y);
    END IF;

    SET @headX := NEW.x, @headY := NEW.y;
END$

CREATE TRIGGER day09_bi BEFORE INSERT ON day09 FOR EACH ROW
BEGIN
    WHILE NEW.distance DO
        SET NEW.distance = NEW.distance - 1;

        UPDATE knots SET
            x = x + CASE NEW.direction WHEN 'L' THEN -1 WHEN 'R' THEN 1 ELSE 0 END,
            y = y + CASE NEW.direction WHEN 'D' THEN -1 WHEN 'U' THEN 1 ELSE 0 END
        WHERE id = 1;

        UPDATE knots SET
            x = (@x := x) + SIGN((@headX - x) * (ABS(@headX - x) = 2 OR ABS(@headX - x) = 1 AND ABS(@headY -  y) = 2)),
            y = y         + SIGN((@headY - y) * (ABS(@headY - y) = 2 OR ABS(@headY - y) = 1 AND ABS(@headX - @x) = 2))
        WHERE id > 1
        ORDER BY id;

    END WHILE;
END$

DELIMITER ;

LOAD DATA INFILE 'c:/ProgramData/MySQL/MySQL Server 8.0/Uploads/day09.txt'
INTO TABLE day09
FIELDS TERMINATED BY ' ';

SELECT 
    (knotId = 10) + 1 AS part, 
    COUNT(DISTINCT x, y) AS solution 
FROM knotRecord 
GROUP BY knotId;