Started “Advent of Code” with the kid, the kid is polishing the Python (and English) skills. I thought I better do this in SQL.
The Task 1 for “Day 1” is: you get 200
numbers from the accounting department, find the two which in sum are 2020. This two numbers multiplied is the result of the task.
Task 2 is like task 1, except it’s using three numbers. All three in sum will be 2020
, and then multiply these three and that is the result.
Preparation
It’s possible to resolve this on the commandline in the database, but given that I deal with 200
numbers I decided to feed them into a table:


The result is 200
lines with numbers in the accounting
table.


Task 1
A CTE in PostgreSQL allows to build a subresult which is independent from the other data sets. It’s like a table which is created on the fly, and can be used in the main query as a table. In order to find the two numbers which sum to 2020
, I join two result sets of the table data:


This will produce a cartesian (or cross) join of all rows from l1
with l2
, a total of 40.000
results. This result set needs to be limited to all rows where l1.value
and l2.value
together is 2020
:


And finally I need the multiplication of l1.value
and l2.value
:


The result is:


55776
is the result I’m looking for. Of course this result appears twice, because I run over the same data in both loops.
Task 2
Basically task 2 is the same as task 1, except that I need to loop 3 times (3 CTE instead of 2), and compare three numbers instead of two:


The result:


Given that we run three times over the data, I now get 6 times the same result. The query runs a little bit longer, producing a cross join with 8.000.000
rows this time. And 223162626
is the number I’m looking for.
Notes
My PostgreSQL 12 does not optimize the CTEs, but does materialize all two respective three of them. That is a bit slow.