"Querying HackerNews with DuckDB directly (HackerTube, HackerBooks \u0026 Wikihack)"

Reading Karpathy’s HackerTube, and then seeing a bunch of other apps on top of Hacker News like HackerBooks and Wikihacks:

Karim Jedda noted on X “simpler to just publish the curated/filtered down datasets and let client” do the rest. That got me thinking: the dataset is public on Hugging Face, and with DuckDB we can just query it directly from your terminal.

Let’s see how that looks and works.

Let’s read all rows available in that dataset for 2026 (run it yourself below with integrated DuckDB runner) - just replace 2026 with ** to read all data.

Gives the result of story domains: ssp.sh 108, sspaeti.com 8, dedp.online 5.

Rest ≤ 8 points. Posters: mostly articsputnik, then zazuke, sebg, sspaeti.

Let’s now rebuild the HackerTube, HackerBooks & Wikihacks in three DuckDB queries, no UI needed 😉.

-- shared: one row per link (story url + hrefs in comments)
CREATE OR REPLACE TEMP TABLE links AS
SELECT id, unnest(list_concat(
  CASE WHEN url <> '' THEN [url] ELSE [] END,
  regexp_extract_all(replace(replace(text, '&#x2F;', '/'), '&amp;', '&'), 'href="([^"]+)"', 1)
)) AS link
FROM 'hf://datasets/open-index/hacker-news/data/2026/2026-07.parquet';

-- 1) HackerTube: titles via YouTube oEmbed (no API key)
CREATE OR REPLACE TEMP TABLE top_videos AS
SELECT coalesce(nullif(regexp_extract(link, '[?&]v=([A-Za-z0-9_-]{11})', 1), ''),
                regexp_extract(link, 'youtu\.be/([A-Za-z0-9_-]{11})', 1)) AS video_id,
       count(*) AS mentions
FROM links
WHERE regexp_matches(link, 'youtube\.com/watch|youtu\.be/') AND video_id <> ''
GROUP BY ALL ORDER BY mentions DESC LIMIT 10;

SET VARIABLE yt_urls = (SELECT list('https://www.youtube.com/oembed?format=json&url=https://youtube.com/watch?v=' || video_id) FROM top_videos);

SELECT v.mentions, j.title, j.author_name AS channel, 'https://youtube.com/watch?v=' || v.video_id AS url
FROM top_videos v
LEFT JOIN read_json(getvariable('yt_urls'), filename=true, union_by_name=true, ignore_errors=true) j
  ON regexp_extract(j.filename, 'v=(.*)$', 1) = v.video_id
ORDER BY mentions DESC;

-- 2) Wikihack: title = url-decoded slug, no API
SELECT count(*) AS mentions,
       replace(url_decode(regexp_extract(link, 'wikipedia\.org/wiki/([^#?]+)', 1)), '_', ' ') AS title,
       'https://en.wikipedia.org/wiki/' || regexp_extract(link, 'wikipedia\.org/wiki/([^#?]+)', 1) AS url
FROM links
WHERE regexp_matches(link, 'wikipedia\.org/wiki/') AND title <> ''
GROUP BY ALL ORDER BY mentions DESC LIMIT 10;

-- 3) HackerBooks: ISBN -> OpenLibrary; ASIN/Goodreads -> URL slug
CREATE OR REPLACE TEMP TABLE top_books AS
SELECT coalesce(nullif(regexp_extract(link, '/dp/([A-Z0-9]{10})', 1), ''),
                regexp_extract(link, 'goodreads\.com/book/show/(\d+)', 1)) AS book_id,
       any_value(link) AS url,
       count(*) AS mentions
FROM links
WHERE regexp_matches(link, 'amazon\.[a-z.]+/.*dp/|goodreads\.com/book/show') AND book_id <> ''
GROUP BY ALL ORDER BY mentions DESC, book_id LIMIT 10;  -- tiebreak: ISBNs before ASINs

SET VARIABLE ol_urls = (SELECT coalesce(list('https://openlibrary.org/isbn/' || book_id || '.json'), ['https://openlibrary.org/isbn/0000000000.json']) FROM top_books WHERE book_id ~ '^[0-9]{9}[0-9X]$');

SELECT b.mentions,
       coalesce(j.title,
                nullif(replace(regexp_extract(b.url, 'book/show/\d+-([^/?]+)', 1), '-', ' '), ''),
                nullif(replace(regexp_extract(b.url, 'amazon\.[a-z.]+/([^/]+)/dp/', 1), '-', ' '), '')
       ) AS title,
       CASE WHEN j.title IS NOT NULL THEN 'openlibrary' ELSE 'url slug' END AS title_src,
       b.url
FROM top_books b
LEFT JOIN read_json(getvariable('ol_urls'), filename=true, union_by_name=true, ignore_errors=true) j
  ON regexp_extract(j.filename, 'isbn/(.*)\.json', 1) = b.book_id
ORDER BY mentions DESC;