I have a 1987 Honda Elite 150 (CH 150) “Spacey” moped that I love to cruise around on. I struggled to find a service manual online. So I bought a copy on Ebay and scaned all of the pages. I then ran it through OCRmyPDF to make it easier to search. That PDF can be downloaded here. If you want the raw scanned pdf it can be downloaded here....

I enjoyed this one! Windowing functions worked nicely for this. WITH raw_input AS ( SELECT """30373 25512 65332 33549 35390""" AS raw_text ), raw_rows AS ( SELECT o, raw_row FROM ( SELECT SPLIT(raw_text, '\n') raw_row_array FROM raw_input ), UNNEST(raw_row_array) raw_row WITH OFFSET o ), split_rows AS ( SELECT SPLIT(raw_row, '') as split_row, raw_row, o FROM raw_rows ), unnested_rows AS ( SELECT raw_row, o AS row_num, col_num, CAST(col AS INT) AS col FROM split_rows, UNNEST(split_row) col WITH offset col_num ), agg_rows as ( SELECT ARRAY_AGG(col) OVER (PARTITION BY col_num ORDER BY row_num ASC) AS down, ARRAY_AGG(col) OVER (PARTITION BY col_num ORDER BY row_num DESC) AS up, ARRAY_AGG(col) OVER (PARTITION BY row_num ORDER BY col_num ASC) AS left_, ARRAY_AGG(col) OVER (PARTITION BY row_num ORDER BY col_num DESC) AS right_, col, col_num, row_num, raw_row FROM unnested_rows ), part_1 AS ( SELECT COUNT(1) FROM agg_rows WHERE -- if evaluates to null, is an edge tree IFNULL( col > (SELECT MAX(down[SAFE_OFFSET(o - 1)]) FROM UNNEST(down) x WITH OFFSET o) -- visible down OR col > (SELECT MAX(up[SAFE_OFFSET(o - 1)]) FROM UNNEST(up) x WITH OFFSET o) -- visible up, OR col > (SELECT MAX(left_[SAFE_OFFSET(o - 1)]) FROM UNNEST(left_) x WITH OFFSET o) -- visible left, OR col > (SELECT MAX(right_[SAFE_OFFSET(o - 1)]) FROM UNNEST(right_) x WITH OFFSET o) -- visible right, , TRUE) ), max_counts as ( SELECT MAX(row_num) max_row, MAX(col_num) max_col FROM agg_rows ), line_of_sight AS ( SELECT row_num - IFNULL((SELECT MAX(o) FROM UNNEST(down) x WITH OFFSET o WHERE x >= col AND o <> row_num), 0) AS down_visible, (SELECT MAX(o) FROM UNNEST(up) x WITH OFFSET o WHERE x >= col AND o <> (max_row - row_num)) as up, (max_row - row_num) - IFNULL((SELECT MAX(o) FROM UNNEST(up) x WITH OFFSET o WHERE x >= col AND o <> (max_row - row_num)), 0) AS up_visible, col_num - IFNULL((SELECT MAX(o) FROM UNNEST(left_) x WITH OFFSET o WHERE x >= col AND o <> col_num), 0) AS left_visible, (max_col - col_num) - IFNULL((SELECT MAX(o) FROM UNNEST(right_) x WITH OFFSET o WHERE x >= col AND o <> (max_col - col_num)), 0) AS right_visible, * FROM agg_rows, max_counts ) -- SELECT * FROM part_1 SELECT MAX(down_visible * up_visible * left_visible * right_visible) AS max_scenic_score FROM line_of_sight ...

I have fallen behind since day 6, but am attempting to make up some ground. Day 7 proved to be pretty tough. I kept track of the “current” directory by appending all of the cd commands. I then removed the directory before the .., ie one/dir/.. -> one. I attempted to use a recursive regex for this, but unfortunatly BigQuery uses the re2 regex library which does not support recursive syntax....

Okay, hope in SQL has been temporarily restored. This problem fits SQL a bit better so I did not have to do anything too wild. For part 2, I decided to copy/past LAG(4) - LAG(13). There might be a better way to do it but I decided to keep it simple for myself. WITH raw_input AS ( SELECT 'mjqjpqmgbljsphdztnvjfqwrcgsmlb' AS raw_row ), points as ( SELECT TO_CODE_POINTS(raw_row) p FROM raw_input ), point_rows AS ( SELECT CHR(cp) cp, o FROM points, UNNEST(p) cp WITH offset o ), points_grouped AS ( SELECT CONCAT( LAG(cp, 1) OVER (ORDER BY o), LAG(cp, 2) OVER (ORDER BY o), LAG(cp, 3) OVER (ORDER BY o), LAG(cp, 4) OVER (ORDER BY o), LAG(cp, 5) OVER (ORDER BY o), LAG(cp, 6) OVER (ORDER BY o), LAG(cp, 7) OVER (ORDER BY o), LAG(cp, 8) OVER (ORDER BY o), LAG(cp, 9) OVER (ORDER BY o), LAG(cp, 10) OVER (ORDER BY o), LAG(cp, 11) OVER (ORDER BY o), LAG(cp, 12) OVER (ORDER BY o), LAG(cp, 13) OVER (ORDER BY o), cp ) str, o FROM point_rows ) SELECT o + 1 FROM points_grouped WHERE ARRAY_LENGTH(ARRAY(( SELECT DISTINCT element FROM UNNEST(SPLIT(str,'')) as element ))) = 14 ORDER BY o LIMIT 1 ...

Oof, this one was rough. This may be the start of the end for my SQL Advent of Code journey. I tried a few different approaches before “cheating” and falling back on a Javascript UDF. This code is a mess and I would definitly not recommend reading it. I don’t have the stomach to go back and read it myself to clean it up. CREATE TEMP FUNCTION move_box(directions ARRAY<STRUCT<quantity INT64, move_from INT64, move_to INT64>>, start_positions ARRAY<STRUCT<c INT64, value STRING>>) RETURNS STRING LANGUAGE js AS r""" let x = {}; for (let i = 0; i < start_positions....

This year, I am be taking a page out of Felipe Hoffa’s book and will be attempting to complete the Advent of Code using BigQuery SQL. Until I reach the point where I can’t solve the puzzles with SQL, I’ll keep track of things here. Day 1 WITH raw_input AS ( /* To start, I just copy/paste the input as a raw text string, and then parse that string into rows....

The 2022 ARVO conference is taking place in Denver, CO. I am excited to be there networking and learning from the scientists in the field. Here are a few of the Choroideremia related posters and talks that I will be checking out: Sunday Cone inner-segment area and circularity in chorioderemia assessed with split-detection adaptive optics scanning light ophthalmoscopy - Peiluo Xu CHM cones are bigger and more circular. Visualization of the Photoreceptor / RPE / Choriocapillaris Complex in Choroideremia Using Adaptive Optics Enhanced Indocyanine Green Imaging - Nancy Aguilera The RPE was the most disrupted layer....

This is a living document of BigQuery resources that I have found useful (with some comments about their usefulness). “Under the hood” articles Anatomy of a BigQuery Query: Why is BigQuery cool? BigQuery under the hood: High-level descriptioon of how BigQuery is able to be so fast. In memory query execution: If you like map reduce you should read this article. It talks about what is unique about BigQuery’s shuffle....