Detect three consecutive results

DROP TABLE IF EXISTS temp_rand; -- 16ms
CREATE TABLE temp_rand ( --  0.25s
    "row_num"   BIGINT      PRIMARY KEY,
    "gid"       INTEGER     NOT NULL,
    "id"        INTEGER     NOT NULL,
    "rid"       INTEGER     NOT NULL
);
INSERT INTO temp_rand -- 19.59s
SELECT
    num AS "row_num",
    RANDOM(1, 3) AS "gi",
    RANDOM(1, 12) AS "id",
    RANDOM(1, 2) AS "ri"
FROM master.integers_100m
;
DROP TABLE IF EXISTS find_triples; -- 31ms
CREATE TABLE find_triples ( -- 0.39s
    "row_num"   BIGINT      PRIMARY KEY,
    "group"     VARCHAR(10) NOT NULL,
    "id"        INTEGER     NOT NULL,
    "result"    CHAR(1)     NOT NULL
);
INSERT INTO find_triples -- 31.71s
WITH grp AS (
    SELECT 1 AS "gid", 'Green' AS "group"   UNION ALL
    SELECT 2 AS "gid", 'Blue'  AS "group"   UNION ALL
    SELECT 3 AS "gid", 'Red'   AS "group"
),
res AS (
    SELECT 1 AS "rid", 'A' AS "result"      UNION ALL
    SELECT 2 AS "rid", 'B' AS "result"
)
SELECT
    temp_rand.row_num,
    grp.GROUP,
    temp_rand.id,
    res.RESULT
FROM temp_rand
JOIN grp ON grp.gid = temp_rand.gid
JOIN res ON res.rid = temp_rand.rid
;
-- Check distribution
SELECT id,     COUNT(1) FROM find_triples GROUP BY 1; -- 0.23s
SELECT RESULT, COUNT(1) FROM find_triples GROUP BY 1; -- 0.23s
SELECT GROUP,  COUNT(1) FROM find_triples GROUP BY 1; -- 1.35s
-- Count triples: cases where the result & group are the same on three consecutive rows
-- In the case of 4, 5, 6 return 2, 3, 4 for now
WITH tests AS ( -- 56.02s
    SELECT
        CASE
            WHEN LEAD(RESULT) OVER (ORDER BY row_num) IS NULL   THEN 0
            WHEN LAG(RESULT)  OVER (ORDER BY row_num) IS NULL   THEN 0
            WHEN LEAD(RESULT) OVER (ORDER BY row_num) != RESULT THEN 0
            WHEN LAG(RESULT)  OVER (ORDER BY row_num) != RESULT THEN 0
            WHEN LEAD(GROUP)  OVER (ORDER BY row_num) != GROUP  THEN 0
            WHEN LAG(GROUP)   OVER (ORDER BY row_num) != GROUP  THEN 0
            ELSE 1 END AS "triple_test"
    FROM find_triples
)
SELECT
    SUM(triple_test) AS "triple_count"
FROM tests
;
-- Now return the count for each group
WITH tests AS ( -- 1m 2s
    SELECT
        GROUP,
        CASE
            WHEN LEAD(RESULT) OVER (ORDER BY row_num) IS NULL   THEN 0
            WHEN LAG(RESULT)  OVER (ORDER BY row_num) IS NULL   THEN 0
            WHEN LEAD(RESULT) OVER (ORDER BY row_num) != RESULT THEN 0
            WHEN LAG(RESULT)  OVER (ORDER BY row_num) != RESULT THEN 0
            WHEN LEAD(GROUP)  OVER (ORDER BY row_num) != GROUP  THEN 0
            WHEN LAG(GROUP)   OVER (ORDER BY row_num) != GROUP  THEN 0
            ELSE 1 END AS "triple_test"
    FROM find_triples
)
SELECT
    GROUP,
    SUM(triple_test) AS "triple_count"
FROM tests
GROUP BY GROUP
;
DROP TABLE IF EXISTS temp_rand; -- 0.17s
DROP TABLE IF EXISTS find_triples; -- 0.19s