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.
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
A CTE in PostgreSQL allows to build a sub-result 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
l2, a total of
40.000 results. This result set needs to be limited to all rows where
l2.value together is
And finally I need the multiplication of
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.
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:
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.
My PostgreSQL 12 does not optimize the CTEs, but does materialize all two respective three of them. That is a bit slow.