Advent of code - Day 4 using Flink SQL and Match Recognize

day4.sql

-- https://adventofcode.com/2019/day/4

SELECT COUNT(DISTINCT CAST(
        ((codes.passcode[1] * 100000) +
        (codes.passcode[2] * 10000) +
        (codes.passcode[3] * 1000) +
        (codes.passcode[4] * 100) + 
        (codes.passcode[5] * 10) + 
        codes.passcode[6]) AS VARCHAR)
FROM digits
    MATCH_RECOGNIZE (
    ORDER BY proctime
    MEASURES 
        ARRAY[
            FIRST(DIGIT.n, 0),
            FIRST(DIGIT.n, 1), 
            FIRST(DIGIT.n, 2),
            FIRST(DIGIT.n, 3), 
            FIRST(DIGIT.n, 4), 
            FIRST(DIGIT.n, 5)] AS passcode
    ONE ROW PER MATCH
    AFTER MATCH TO NEXT ROW
    PATTERN (DIGIT{6,6})
    DEFINE
        DIGIT AS DIGIT.n >= LAST(DIGIT.n, 1) OR LAST(DIGIT.n, 1) IS NULL
) AS codes
WHERE
    codes.passcode[1] = codes.passcode[2] OR
    codes.passcode[2] = codes.passcode[3] OR
    codes.passcode[3] = codes.passcode[4] OR
    codes.passcode[4] = codes.passcode[5] OR
    codes.passcode[5] = codes.passcode[6]