AoC2024, day1 PostgreSQL 17

day1.sql

WITH
lines AS (
  SELECT regexp_split_to_array(line,'   ') AS ns
    FROM regexp_split_to_table(pg_read_file('/tmp/day1.txt'),'\n') line),
nums AS (
  SELECT ns[1]::integer as l, ns[2]::integer as r FROM lines),
lefts AS (
  SELECT l as n, row_number() over(order by l) as pos FROM nums),
rights AS (
  SELECT r as n, row_number() over(order by r) as pos FROM nums)
-- PART 1
SELECT sum(abs(n1.n - n2.n)) AS answer
  FROM lefts n1 JOIN rights n2 ON n1.pos=n2.pos
UNION ALL
-- PART 2
SELECT SUM(n1.n * (SELECT COUNT(n) FROM rights n2 WHERE n2.n=n1.n)) AS answer
  FROM lefts n1