Query proof
Trace a phrase on Hacker News with SQL
A Scry query pattern for tracing a public phrase through Hacker News by month, distinct authors, and inspectable source rows.
Answer shape
Scry can turn a phrase into a bounded Hacker News time series while keeping the HN ID, author, title, timestamp, and URI available for inspection.
Sources
- Hacker News
Methods
- source-native SQL
- phrase filtering
- author aggregation
- time series
SQL
Run this through POST /v1/scry/query after inspecting GET /v1/scry/schema for current live fields.
WITH mentions AS (
SELECT hn_id, title, original_author, original_timestamp, uri
FROM hackernews.items
WHERE is_deleted = false
AND original_timestamp IS NOT NULL
AND positionCaseInsensitive(
concat(title, ' ', payload),
'mechanistic interpretability'
) > 0
ORDER BY original_timestamp ASC, hn_id ASC
LIMIT 5000
)
SELECT toStartOfMonth(original_timestamp) AS month,
count() AS mentions,
uniq(original_author) AS authors,
argMin(tuple(hn_id, original_author, title, uri), original_timestamp) AS first_mention
FROM mentions
GROUP BY month
ORDER BY month ASC
LIMIT 120;