In my previous post, I introduced you to the Advent of Code programming challenge, and explained how I’m going through the days, solving both parts of each puzzle. Continuing on, today we have day 15. In this challenge, we are working on the cookie recipe. Specifically, different ingredients impact a few different aspects / properties of the cookie (flavor, texture, capacity (to absorb milk) and durability (how well the cookie stay intact when full of milk). We are provided an input file that for each ingredient, breaks down how 1 teaspoon of that ingredient impacts the various aspects. The input file is formatted like this:
|
1 |
Frosting: capacity 4, durability -2, flavor 0, texture 0, calories 5 |
Each cookie needs 100 teaspoons of the combination of ingredients. The task is to find the combination that when considering the number of teaspoons of the ingredients and how this impacts the properties of the cookies, gives the highest score for the cookie. The score is determined by adding together, for each property, all the the ingredients. Negative amounts of the total are considered zero, and then the properties are multiplied together to determine the score.
The first step will be to load the input file, separate the lines, and then from each line extract out the various parts. For that, we proceed as we have through most of these puzzles – loading the data into a temporary table, and then determining the position of various characters in the line with the CHARINDEX function and extracting this data with the SUBSTRING function. The CROSS APPLY is just introducing those columns into the query so that they can be subsequently used in multiple places, while only being calculated once. Note that several of the CHARINDEX calls use the optional third parameter which specifies the starting position. In these cases, the code is looking for a comma, so we are specifying the position of the last comma + 1 as the starting position for the next comma:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL DROP TABLE #temp; CREATE TABLE #temp ( Ingredient VARCHAR(20), Capacity INTEGER, Durability INTEGER, Flavor INTEGER, Texture INTEGER, Calories INTEGER ); INSERT INTO #temp (Ingredient, Capacity, Durability, Flavor, Texture, Calories) SELECT LEFT(Item, ca1.ColonPos-1) AS Ingredient , SUBSTRING(Item, CapacityPos+9, CapacityCommaPos-CapacityPos-9) AS Capacity , SUBSTRING(Item, DurabilityPos+10, DurabilityCommaPos-DurabilityPos-10) AS Durability , SUBSTRING(Item, FlavorPos+7, FlavorCommaPos-FlavorPos-7) AS Flavor , SUBSTRING(Item, TexturePos+8, TextureCommaPos-TexturePos-8) AS Texture , SUBSTRING(Item, CaloriesPos+9, 8000) AS Calories FROM OPENROWSET(BULK 'D:\AdventOfCode\Day15.txt', SINGLE_CLOB) InputFile(FileText) CROSS APPLY Sandbox.dbo.DelimitedSplit8K(REPLACE(InputFile.FileText, CHAR(10), ''), CHAR(13)) CROSS APPLY (VALUES (CHARINDEX(':', Item), CHARINDEX('capacity', Item), CHARINDEX(',', Item)) ) ca1(ColonPos, CapacityPos, CapacityCommaPos) CROSS APPLY (VALUES (CHARINDEX('durability', Item), CHARINDEX(',', Item, ca1.CapacityCommaPos+1)) ) ca2(DurabilityPos, DurabilityCommaPos) CROSS APPLY (VALUES (CHARINDEX('flavor', Item), CHARINDEX(',', Item, ca2.DurabilityCommaPos+1)) ) ca3(FlavorPos, FlavorCommaPos) CROSS APPLY (VALUES (CHARINDEX('texture', Item), CHARINDEX(',', Item, ca3.FlavorCommaPos+1), CHARINDEX('calories', Item)) ) ca4(TexturePos, TextureCommaPos, CaloriesPos); |
In the next phase, I use a virtual tally table of the numbers 1-100, and cross join this to itself once for each ingredient, and the resulting numbers are the teaspoons to be used for each ingredient. Since the number of teaspoons used needs to equal 100, the code only gets the values where the four numbers add up to 100. Additionally, a ROW_NUMBER is calculated for each row. This will be used to be able to perform aggregations and to group upon. Next, each property needs to be added up across all ingredients (the property for that ingredient multiplied by the number of teaspoons used for that ingredient). This requires performing a CROSS JOIN to the input file. Finally, all of the properties are multiplied across each other (a zero is substituted for negative sums), and the highest one is returned with the MAX function.
|
33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 |
WITH Tens (N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), Hundreds(N) AS (SELECT 1 FROM Tens t1, Tens t2), Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM Hundreds) , cte1 AS ( SELECT t1.N AS Frosting , t2.N AS Candy , t3.N AS Butterscotch , t4.N AS Sugar , ROW_NUMBER() OVER (ORDER BY t1.N, t2.N, t3.N, t4.N) AS RN FROM Tally t1, Tally t2, Tally t3, Tally t4 WHERE t1.N + t2.N + t3.N + t4.N = 100 ), cte2 AS ( SELECT RN , SUM(CASE WHEN Ingredient = 'Frosting' THEN Capacity * Frosting WHEN Ingredient = 'Candy' THEN Capacity * Candy WHEN Ingredient = 'Butterscotch' THEN Capacity * Butterscotch WHEN Ingredient = 'Sugar' THEN Capacity * Sugar END) AS Capacity , SUM(CASE WHEN Ingredient = 'Frosting' THEN Durability * Frosting WHEN Ingredient = 'Candy' THEN Durability * Candy WHEN Ingredient = 'Butterscotch' THEN Durability * Butterscotch WHEN Ingredient = 'Sugar' THEN Durability * Sugar END) AS Durability , SUM(CASE WHEN Ingredient = 'Frosting' THEN Flavor * Frosting WHEN Ingredient = 'Candy' THEN Flavor * Candy WHEN Ingredient = 'Butterscotch' THEN Flavor * Butterscotch WHEN Ingredient = 'Sugar' THEN Flavor * Sugar END) AS Flavor , SUM(CASE WHEN Ingredient = 'Frosting' THEN Texture * Frosting WHEN Ingredient = 'Candy' THEN Texture * Candy WHEN Ingredient = 'Butterscotch' THEN Texture * Butterscotch WHEN Ingredient = 'Sugar' THEN Texture * Sugar END) AS Texture FROM cte1 CROSS JOIN #temp t1 GROUP BY RN ) SELECT MAX(CASE WHEN Capacity < 0 THEN 0 ELSE Capacity END * CASE WHEN Durability < 0 THEN 0 ELSE Durability END * CASE WHEN Flavor < 0 THEN 0 ELSE Flavor END * CASE WHEN Texture < 0 THEN 0 ELSE Texture END) AS TotalScore FROM cte2 |
For part 2, you are asked to restrict the cookies to 500 calories. What is the score of the highest scoring cookie that is 500 calories?
To handle this modification, two minor changes are needed. Since the calories are already being stored in the input table, no changes are necessary in that section. In cte2, we need to calculate the calories for the teaspoons used and to sum up the calories across all of the ingredients, and in the final query we need to add a predicate to only get the max for cookies with 500 calories. To make these changes, insert lines 1-5 below between lines 69-70 of cte2, and add line 5 to the end:
|
1 2 3 4 5 6 |
, SUM(CASE WHEN Ingredient = 'Frosting' THEN Calories * Frosting WHEN Ingredient = 'Candy' THEN Calories * Candy WHEN Ingredient = 'Butterscotch' THEN Calories * Butterscotch WHEN Ingredient = 'Sugar' THEN Calories * Sugar END) AS Calories WHERE Calories = 500; |
Now that the cookie recipe has been mastered, day 15 is complete. Come back Monday for day 16.
