using sqlite3 to solve an advent of code problem
Soon we will be heading into advent season. Which means it is time for Advent of Code again! Yay.
I thought of last year and how I had used sqlite3 to solve one of those problems and thought I would write it up.
The first part of the first problem is a good place to start.
https://adventofcode.com/2021/day/1
You get a list of numbers from a submarine that represent depth numbers. You have to determine the number of times the depth INCREASES.
The first thing I did was get the example numbers stored into a simple text file.
You can import a CSV file into a table in sqlite3 like this.
CREATE TABLE measures (
val INTEGER );
.mode csv
.import realdata.txt measures
Those statements will create a table called measures that will hold all of our depth measurements from the submarine.
Then it will load the integer values into the table with one per row. Your table will look like this
sqlite> select * from measures;
val
199
200
208
210
200
207
240
269
260
263
Next we want to do some math on that value. Specifically we want to subtract the value in the previous row.
SQL has no real concepts of ordering so you have to be specific. We will use a lag plus an explicit order so sqlite3 will give us the correct answer.
This is the first step we will build up to a final answer as we go.
sqlite> SELECT val AS a, lag(val) OVER (ORDER BY rowid) AS b FROM measures;
199|
200|199
208|200
210|208
200|210
207|200
240|207
269|240
260|269
263|260
The query above selects the depth column and a lag of the depth column using the built in rowid from the measurements.
The LAG function will select the result from row in the partition. In our case we are ordering by rowid so it will always end up giving us the previous value or NULL in the case of the first row.
This step was to get both values into a single row, we will use this to compute the difference.
sqlite> SELECT CASE WHEN a-b>0 THEN 1 ELSE 0 END AS c FROM
...> (SELECT val AS a, lag(val) OVER (ORDER BY rowid) AS b FROM measures);
0
1
1
1
0
1
1
1
0
1
Now we use a CASE WHEN statement to output a 1 or 0 depending if there is an increase in depth or not.
And then we are at the last step, we just need to sum up the values and we will have our answer.
The full query is listed here
SELECT sum(c) FROM (SELECT CASE WHEN a-b>0 THEN 1 ELSE 0 END AS c FROM
(SELECT val AS a, lag(val) OVER (ORDER BY rowid) AS b FROM measures));
For the example provided this will output 7 which is correct!
Amazing things can be done with sqlite3.