Materialize/test/sqllogictest/advent-of-code/2023/aoc_1202.slt

# 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_1202.md

mode cockroach

statement ok
CREATE TABLE input (input TEXT);

statement ok
INSERT INTO input VALUES ('Game 1: 1 red, 4 green, 4 blue; 3 red, 4 blue; 2 red, 1 green; 1 green, 1 red');

statement ok
INSERT INTO input VALUES ('Game 2: 20 green, 1 red; 1 blue, 4 red; 1 green, 1 red, 1 blue; 1 green, 1 red, 1 blue');

statement ok
INSERT INTO input VALUES ('Game 3: 8 red, 1 blue, 5 green; 9 red, 3 blue, 3 green; 3 green, 4 red');

statement ok
INSERT INTO input VALUES ('Game 4: 0 blue, 8 red, 4 green; 7 green, 8 blue, 9 red; 2 green, 5 red, 1 blue');

statement ok
INSERT INTO input VALUES ('Game 5: 20 red, 10 blue, 9 green; 2 blue, 3 red, 7 green; 1 green, 3 blue');

statement ok
CREATE TABLE aoc_1202 (game_id TEXT, set_id TEXT, green_cnt INT, red_cnt INT, blue_cnt INT);

statement ok
INSERT INTO aoc_1202 VALUES ('Game 1', 'set_0', 4, 1, 4), ('Game 1', 'set_1', 0, 3, 4), ('Game 1', 'set_2', 1, 2, 0), ('Game 1', 'set_3', 1, 1, 0);

statement ok
INSERT INTO aoc_1202 VALUES ('Game 2', 'set_0', 20, 1, 0), ('Game 2', 'set_1', 0, 4, 1), ('Game 2', 'set_2', 1, 1, 1), ('Game 2', 'set_3', 1, 1, 1);

statement ok
INSERT INTO aoc_1202 VALUES ('Game 3', 'set_0', 5, 8, 1), ('Game 3', 'set_1', 3, 9, 3), ('Game 3', 'set_2', 3, 4, 0);

statement ok
INSERT INTO aoc_1202 VALUES ('Game 4', 'set_0', 4, 8, 0), ('Game 4', 'set_1', 7, 9, 8), ('Game 4', 'set_2', 2, 5, 1);

statement ok
INSERT INTO aoc_1202 VALUES ('Game 5', 'set_0', 9, 20, 10), ('Game 5', 'set_1', 7, 3, 2), ('Game 5', 'set_2', 1, 0, 3);

query I
WITH game_cnt AS (
SELECT split_part(game_id,' ', 2)::int AS game_id,
       COUNT(set_id) AS total_set_cnt,
       COUNT(set_id) FILTER (WHERE (green_cnt <= 13) AND (red_cnt <= 12) AND (blue_cnt <= 14)) AS possible_set_cnt
FROM aoc_1202
GROUP BY game_id
)
SELECT SUM(game_id) FROM game_cnt WHERE total_set_cnt = possible_set_cnt;
----
8

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
WITH game_cnt AS (
        SELECT split_part(game_id,' ', 2)::int AS game_id,
               COUNT(set_id) AS total_set_cnt,
               COUNT(set_id) FILTER (WHERE (green_cnt <= 13) AND (red_cnt <= 12) AND (blue_cnt <= 14)) AS possible_set_cnt
        FROM aoc_1202
        GROUP BY game_id
        )
        SELECT SUM(game_id) FROM game_cnt WHERE total_set_cnt = possible_set_cnt;
----
Explained Query:
  With
    cte l0 =
      Reduce aggregates=[sum(text_to_integer(split_string(#0{game_id}, " ", 2)))] // { arity: 1 }
        Project (#0{game_id}) // { arity: 1 }
          Filter (#1{count_set_id} = #2{count}) // { arity: 3 }
            Reduce group_by=[#0{game_id}] aggregates=[count(#1{set_id}), count(case when ((#2{green_cnt} <= 13) AND (#3{red_cnt} <= 12) AND (#4{blue_cnt} <= 14)) then #1{set_id} else null end)] // { arity: 3 }
              ReadStorage materialize.public.aoc_1202 // { arity: 5 }
  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_1202

Target cluster: quickstart

EOF

query I
WITH game_min AS (
SELECT split_part(game_id,' ', 2)::int AS game_id,
       MAX(green_cnt) AS green_min,
       MAX(red_cnt) AS red_min,
       MAX(blue_cnt) AS blue_min
FROM aoc_1202
GROUP BY split_part(game_id,' ', 2)::int
)
SELECT SUM(green_min*red_min*blue_min) FROM game_min;
----
2567

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
WITH game_min AS (
SELECT split_part(game_id,' ', 2)::int AS game_id,
       MAX(green_cnt) AS green_min,
       MAX(red_cnt) AS red_min,
       MAX(blue_cnt) AS blue_min
FROM aoc_1202
GROUP BY split_part(game_id,' ', 2)::int
)
SELECT SUM(green_min*red_min*blue_min) FROM game_min;
----
Explained Query:
  With
    cte l0 =
      Reduce aggregates=[sum(((#0{max_green_cnt} * #1{max_red_cnt}) * #2{max_blue_cnt}))] // { arity: 1 }
        Project (#1{max_green_cnt}..=#3{max_blue_cnt}) // { arity: 3 }
          Reduce group_by=[text_to_integer(split_string(#0{game_id}, " ", 2))] aggregates=[max(#1{green_cnt}), max(#2{red_cnt}), max(#3{blue_cnt})] // { arity: 4 }
            Project (#0{game_id}, #2{green_cnt}..=#4{blue_cnt}) // { arity: 4 }
              ReadStorage materialize.public.aoc_1202 // { arity: 5 }
  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_1202

Target cluster: quickstart

EOF

query II
with mutually recursive
    -- Parse the input up
    lines(line TEXT) as (select regexp_split_to_table(input, '\n') as line from input),
    games(game TEXT, report TEXT) as (select regexp_split_to_array(line, ':')[1], regexp_split_to_array(line, ':')[2] from lines),
    round(game TEXT, visible TEXT) as (select game, regexp_split_to_table(report, ';') from games),
    bacon(game TEXT, color TEXT) as (select game, regexp_split_to_table(visible, ',') from round),
    parsed(game INT, color TEXT, number INT) as (
        select
            substring(game, 5)::INT as game,
            regexp_split_to_array(color, ' ')[3] as color,
            regexp_split_to_array(color, ' ')[2]::INT as number
        from bacon
    ),
    -- PART 1
    limits(color TEXT, number INT) as (SELECT * FROM (VALUES ('red', 12), ('green', 13), ('blue', 14))),
    bad_news(game INT) as (
        select game
        from parsed, limits
        where parsed.color = limits.color
          AND parsed.number > limits.number
    ),
    plausible(game INT) as (select distinct parsed.game from parsed left join bad_news on(parsed.game = bad_news.game) where bad_news.game IS NULL),
    part1(part1 BIGINT) as (select SUM(game) from plausible),
    -- PART 2
    maximum(game INT, color TEXT, number INT) as (select game, color, max(number) from parsed GROUP BY game, color),
    red(game INT) as (select game from maximum, generate_series(1, number) where color = 'red'),
    blue(game INT) as (select game from maximum, generate_series(1, number) where color = 'blue'),
    green(game INT) as (select game from maximum, generate_series(1, number) where color = 'green'),
    power(game INT, product BIGINT) as (SELECT red.game, count(*) from red, blue, green where red.game = blue.game and blue.game = green.game GROUP BY red.game),
    part2(part2 BIGINT) as (select sum(product)::BIGINT from power)
select * from part1, part2;
----
8  2567

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
with mutually recursive
    -- Parse the input up
    lines(line TEXT) as (select regexp_split_to_table(input, '\n') as line from input),
    games(game TEXT, report TEXT) as (select regexp_split_to_array(line, ':')[1], regexp_split_to_array(line, ':')[2] from lines),
    round(game TEXT, visible TEXT) as (select game, regexp_split_to_table(report, ';') from games),
    bacon(game TEXT, color TEXT) as (select game, regexp_split_to_table(visible, ',') from round),
    parsed(game INT, color TEXT, number INT) as (
        select
            substring(game, 5)::INT as game,
            regexp_split_to_array(color, ' ')[3] as color,
            regexp_split_to_array(color, ' ')[2]::INT as number
        from bacon
    ),
    -- PART 1
    limits(color TEXT, number INT) as (SELECT * FROM (VALUES ('red', 12), ('green', 13), ('blue', 14))),
    bad_news(game INT) as (
        select game
        from parsed, limits
        where parsed.color = limits.color
          AND parsed.number > limits.number
    ),
    plausible(game INT) as (select distinct parsed.game from parsed left join bad_news on(parsed.game = bad_news.game) where bad_news.game IS NULL),
    part1(part1 BIGINT) as (select SUM(game) from plausible),
    -- PART 2
    maximum(game INT, color TEXT, number INT) as (select game, color, max(number) from parsed GROUP BY game, color),
    red(game INT) as (select game from maximum, generate_series(1, number) where color = 'red'),
    blue(game INT) as (select game from maximum, generate_series(1, number) where color = 'blue'),
    green(game INT) as (select game from maximum, generate_series(1, number) where color = 'green'),
    power(game INT, product BIGINT) as (SELECT red.game, count(*) from red, blue, green where red.game = blue.game and blue.game = green.game GROUP BY red.game),
    part2(part2 BIGINT) as (select sum(product)::BIGINT from power)
select * from part1, part2;
----
Explained Query:
  With
    cte l0 =
      Project (#3, #5, #6) // { arity: 3 }
        Map (text_to_integer(substr(#0{game}, 5)), regexp_split_to_array[" ", case_insensitive=false](#2{color}), array_index(#4, 3), text_to_integer(array_index(#4, 2))) // { arity: 7 }
          FlatMap unnest_array(regexp_split_to_array[",", case_insensitive=false](#1{visible})) // { arity: 3 }
            Project (#0, #2) // { arity: 2 }
              FlatMap unnest_array(regexp_split_to_array[";", case_insensitive=false](#1{report})) // { arity: 3 }
                Project (#3, #4) // { arity: 2 }
                  Map (regexp_split_to_array[":", case_insensitive=false](#1{line}), array_index(#2, 1), array_index(#2, 2)) // { arity: 5 }
                    FlatMap unnest_array(regexp_split_to_array["\n", case_insensitive=false](#0{input})) // { arity: 2 }
                      ReadStorage materialize.public.input // { arity: 1 }
    cte l1 =
      Filter (#0{game}) IS NOT NULL // { arity: 3 }
        Get l0 // { arity: 3 }
    cte l2 =
      ArrangeBy keys=[[#0{game}]] // { arity: 1 }
        Project (#0) // { arity: 1 }
          Get l1 // { arity: 3 }
    cte l3 =
      Reduce aggregates=[sum(#0{game})] // { arity: 1 }
        Distinct project=[#0] // { arity: 1 }
          Union // { arity: 1 }
            Negate // { arity: 1 }
              Project (#0) // { arity: 1 }
                Join on=(#0{game} = #1) type=differential // { arity: 2 }
                  implementation
                    %1[#0]UKA » %0:l2[#0{game}]K
                  Get l2 // { arity: 1 }
                  ArrangeBy keys=[[#0]] // { arity: 1 }
                    Distinct project=[#0] // { arity: 1 }
                      Project (#0) // { arity: 1 }
                        Filter (#3{number} > #5{number}) // { arity: 6 }
                          Join on=(#0{game} = #1{game} AND #2{color} = #4{color}) type=delta // { arity: 6 }
                            implementation
                              %0:l2 » %1:l0[#0{game}]K » %2[#0{color}]UK
                              %1:l0 » %2[#0{color}]UK » %0:l2[#0{game}]K
                              %2 » %1:l0[#1{color}]K » %0:l2[#0{game}]K
                            Get l2 // { arity: 1 }
                            ArrangeBy keys=[[#0{game}], [#1{color}]] // { arity: 3 }
                              Filter (#0{game}) IS NOT NULL AND (#1{color}) IS NOT NULL // { arity: 3 }
                                Get l0 // { arity: 3 }
                            ArrangeBy keys=[[#0{color}]] // { arity: 2 }
                              Constant // { arity: 2 }
                                - ("red", 12)
                                - ("blue", 14)
                                - ("green", 13)
            Project (#0) // { arity: 1 }
              Get l0 // { arity: 3 }
    cte l4 =
      Reduce group_by=[#0, #1] aggregates=[max(#2{number})] // { arity: 3 }
        Get l1 // { arity: 3 }
    cte l5 =
      Reduce aggregates=[sum(#0{count})] // { arity: 1 }
        Project (#1{count}) // { arity: 1 }
          Reduce group_by=[#0] aggregates=[count(*)] // { arity: 2 }
            Project (#0) // { arity: 1 }
              Join on=(#0{game} = #1{game} = #2{game}) type=delta // { arity: 3 }
                implementation
                  %0 » %1[#0{game}]K » %2[#0{game}]K
                  %1 » %0[#0{game}]K » %2[#0{game}]K
                  %2 » %0[#0{game}]K » %1[#0{game}]K
                ArrangeBy keys=[[#0{game}]] // { arity: 1 }
                  Project (#0) // { arity: 1 }
                    FlatMap generate_series(1, #1{max}, 1) // { arity: 3 }
                      Project (#0, #2{max}) // { arity: 2 }
                        Filter (#1{color} = "red") // { arity: 3 }
                          Get l4 // { arity: 3 }
                ArrangeBy keys=[[#0{game}]] // { arity: 1 }
                  Project (#0) // { arity: 1 }
                    FlatMap generate_series(1, #1{max}, 1) // { arity: 3 }
                      Project (#0, #2{max}) // { arity: 2 }
                        Filter (#1{color} = "blue") // { arity: 3 }
                          Get l4 // { arity: 3 }
                ArrangeBy keys=[[#0{game}]] // { arity: 1 }
                  Project (#0) // { arity: 1 }
                    FlatMap generate_series(1, #1{max}, 1) // { arity: 3 }
                      Project (#0, #2{max}) // { arity: 2 }
                        Filter (#1{color} = "green") // { arity: 3 }
                          Get l4 // { arity: 3 }
  Return // { arity: 2 }
    CrossJoin type=differential // { arity: 2 }
      implementation
        %0[×]U » %1[×]U
      ArrangeBy keys=[[]] // { arity: 1 }
        Union // { arity: 1 }
          Get l3 // { arity: 1 }
          Map (null) // { arity: 1 }
            Union // { arity: 0 }
              Negate // { arity: 0 }
                Project () // { arity: 0 }
                  Get l3 // { arity: 1 }
              Constant // { arity: 0 }
                - ()
      ArrangeBy keys=[[]] // { arity: 1 }
        Project (#1) // { arity: 1 }
          Map (numeric_to_bigint(#0{sum_count})) // { arity: 2 }
            Union // { arity: 1 }
              Get l5 // { arity: 1 }
              Map (null) // { arity: 1 }
                Union // { arity: 0 }
                  Negate // { arity: 0 }
                    Project () // { arity: 0 }
                      Get l5 // { arity: 1 }
                  Constant // { arity: 0 }
                    - ()

Source materialize.public.input

Target cluster: quickstart

EOF