# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.

# https://github.com/MaterializeInc/advent-of-code-2023/blob/main/week1/aoc_1201.md

mode cockroach

statement ok
CREATE TABLE aoc_1201 (input TEXT);

statement ok
INSERT INTO aoc_1201 VALUES
('noveneiner9'),
('seventwoseven114'),
('1two4two'),
('hell0l1'),
('79430242'),
('159a951'),
('0'),
('seven2seven'),
('h4mb5rg')
;


query I
SELECT SUM(LEFT(r, 1)::int * 10 + RIGHT(r, 1)::int) AS part1
FROM (
	SELECT regexp_replace(input, '[^\d]', '', 'g') AS r
	FROM aoc_1201
);
----
278

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT SUM(LEFT(r, 1)::int * 10 + RIGHT(r, 1)::int) AS part1
FROM (
	SELECT regexp_replace(input, '[^\d]', '', 'g') AS r
	FROM aoc_1201
);
----
Explained Query:
  With
    cte l0 =
      Reduce aggregates=[sum(((text_to_integer(left(regexp_replace["[^\d]", case_insensitive=false, limit=0](#0{input}, ""), 1)) * 10) + text_to_integer(right(regexp_replace["[^\d]", case_insensitive=false, limit=0](#0{input}, ""), 1))))] // { arity: 1 }
        ReadStorage materialize.public.aoc_1201 // { arity: 1 }
  Return // { arity: 1 }
    Union // { arity: 1 }
      Get l0 // { arity: 1 }
      Map (null) // { arity: 1 }
        Union // { arity: 0 }
          Negate // { arity: 0 }
            Project () // { arity: 0 }
              Get l0 // { arity: 1 }
          Constant // { arity: 0 }
            - ()

Source materialize.public.aoc_1201

Target cluster: quickstart

EOF

query I
WITH
    lines AS (
        SELECT regexp_split_to_table(input, '\n') AS line
        FROM aoc_1201
    ),
    slices AS (
        SELECT line, index, substring(line, index, width) AS slice
        FROM
            lines,
            generate_series(1, length(line)) AS index,
            generate_series(1, 5) AS width
    ),
    numbers (t, n) AS (
        VALUES ('0', 0), ('1', 1), ('2', 2), ('3', 3), ('4', 4), ('5', 5), ('6', 6), ('7', 7), ('8', 8), ('9', 9),
               ('zero', 0), ('one', 1), ('two', 2), ('three', 3), ('four', 4), ('five', 5), ('six', 6), ('seven', 7), ('eight', 8), ('nine', 9)
    ),
    findings AS (
        SELECT line, index, n AS number
        FROM slices, numbers
        WHERE slices.slice = numbers.t
    ),
    first AS ( SELECT DISTINCT ON (line) line, number AS f FROM findings ORDER BY line, index ),
    last AS ( SELECT DISTINCT ON (line) line, number AS l FROM findings ORDER BY line, index DESC )
SELECT SUM(f * 10 + l)
FROM first, last
WHERE first.line = last.line
----
391

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
WITH
    lines AS (
        SELECT regexp_split_to_table(input, '\n') AS line
        FROM aoc_1201
    ),
    slices AS (
        SELECT line, index, substring(line, index, width) AS slice
        FROM
            lines,
            generate_series(1, length(line)) AS index,
            generate_series(1, 5) AS width
    ),
    numbers (t, n) AS (
        VALUES ('0', 0), ('1', 1), ('2', 2), ('3', 3), ('4', 4), ('5', 5), ('6', 6), ('7', 7), ('8', 8), ('9', 9),
               ('zero', 0), ('one', 1), ('two', 2), ('three', 3), ('four', 4), ('five', 5), ('six', 6), ('seven', 7), ('eight', 8), ('nine', 9)
    ),
    findings AS (
        SELECT line, index, n AS number
        FROM slices, numbers
        WHERE slices.slice = numbers.t
    ),
    first AS ( SELECT DISTINCT ON (line) line, number AS f FROM findings ORDER BY line, index ),
    last AS ( SELECT DISTINCT ON (line) line, number AS l FROM findings ORDER BY line, index DESC )
SELECT SUM(f * 10 + l)
FROM first, last
WHERE first.line = last.line
----
Explained Query:
  With
    cte l0 =
      Project (#0, #1, #4) // { arity: 3 }
        Join on=(#3{t} = substr(#0{line}, #1{index}, #2{width})) type=delta // { arity: 5 }
          implementation
            %0 » %1[×] » %2[#0{t}]UK
            %1 » %0[×] » %2[#0{t}]UK
            %2 » %0[×] » %1[×]
          ArrangeBy keys=[[]] // { arity: 2 }
            FlatMap generate_series(1, char_length(#0{line}), 1) // { arity: 2 }
              Project (#1) // { arity: 1 }
                Filter (#1{line}) IS NOT NULL // { arity: 2 }
                  FlatMap unnest_array(regexp_split_to_array["\n", case_insensitive=false](#0{input})) // { arity: 2 }
                    ReadStorage materialize.public.aoc_1201 // { arity: 1 }
          ArrangeBy keys=[[]] // { arity: 1 }
            Constant // { arity: 1 }
              - (1)
              - (2)
              - (3)
              - (4)
              - (5)
          ArrangeBy keys=[[#0{t}]] // { arity: 2 }
            Constant // { arity: 2 }
              - ("0", 0)
              - ("1", 1)
              - ("2", 2)
              - ("3", 3)
              - ("4", 4)
              - ("5", 5)
              - ("6", 6)
              - ("7", 7)
              - ("8", 8)
              - ("9", 9)
              - ("one", 1)
              - ("six", 6)
              - ("two", 2)
              - ("zero", 0)
              - ("five", 5)
              - ("four", 4)
              - ("nine", 9)
              - ("eight", 8)
              - ("seven", 7)
              - ("three", 3)
    cte l1 =
      Reduce aggregates=[sum(((#0{f} * 10) + #1{l}))] // { arity: 1 }
        Project (#1, #3) // { arity: 2 }
          Join on=(#0{line} = #2{line}) type=differential // { arity: 4 }
            implementation
              %0[#0{line}]UK » %1[#0{line}]UK
            ArrangeBy keys=[[#0{line}]] // { arity: 2 }
              Project (#0, #2) // { arity: 2 }
                TopK group_by=[#0] order_by=[#1 asc nulls_last] limit=1 // { arity: 3 }
                  Get l0 // { arity: 3 }
            ArrangeBy keys=[[#0{line}]] // { arity: 2 }
              Project (#0, #2) // { arity: 2 }
                TopK group_by=[#0] order_by=[#1 desc nulls_first] limit=1 // { arity: 3 }
                  Get l0 // { arity: 3 }
  Return // { arity: 1 }
    Union // { arity: 1 }
      Get l1 // { arity: 1 }
      Map (null) // { arity: 1 }
        Union // { arity: 0 }
          Negate // { arity: 0 }
            Project () // { arity: 0 }
              Get l1 // { arity: 1 }
          Constant // { arity: 0 }
            - ()

Source materialize.public.aoc_1201

Target cluster: quickstart

EOF