DEV Community: Omer Hochman

Top N per group is the query `LIMIT` can't write

Omer Hochman — Mon, 27 Jul 2026 17:19:13 +0000

Originally published at nlqdb.com/blog

You want the top 3 best-selling products in each category. Or the most recent order per customer. Or the highest-scoring attempt per user. The English is so plain it feels like it should compile to something you already know — ORDER BY revenue DESC LIMIT 3 — and that is exactly the trap. LIMIT caps the whole result set. Ask it for the top 3 and it hands you the 3 best rows across every category combined, not 3 per category. The word "per" quietly moved the query somewhere LIMIT cannot follow.

-- What you wrote (wrong): 3 rows total, not 3 per category
SELECT category, name, revenue
FROM products
ORDER BY revenue DESC
LIMIT 3;

"Per group" is a partition, not a limit

This is the classic "greatest-N-per-group" problem, and the reason it gets re-Googled every time is that the correct shape looks nothing like the question. You are not limiting rows — you are ranking within each group and keeping the top of each rank. That is a window function: number the rows inside each partition, then filter to the rank you want.

-- Rank inside each category, then keep the top 3 of each
SELECT category, name, revenue
FROM (
  SELECT category, name, revenue,
         ROW_NUMBER() OVER (
           PARTITION BY category
           ORDER BY revenue DESC
         ) AS rn
  FROM products
) t
WHERE rn <= 3;

The PARTITION BY is the "per category" the English asked for; the ORDER BY inside the window is the "best-selling"; the outer WHERE rn <= 3 is the "top 3." You cannot filter on rn in the same SELECT that computes it — window functions are evaluated after WHERE — so it has to be a subquery (or a CTE). That structural jump, from a flat query to a nested ranked one, is the whole difficulty. Nothing here is hard; it just doesn't resemble the sentence you started with.

The decision hiding in "top 3": what happens to ties

There is a second choice buried in that query, and it is the one that bites in production. If two products tie for third place by revenue, do you want exactly 3 rows, or all the rows tied at rank 3? ROW_NUMBER() breaks ties arbitrarily and gives you exactly 3 — but which of the tied rows it drops is undefined unless your ORDER BY is fully deterministic. RANK() keeps every tied row (so "top 3" might return 4). DENSE_RANK() keeps ties but doesn't skip rank numbers. Same English, three different answers, and the query never tells you which one you picked — you have to have decided.

ROW_NUMBER() — exactly N rows per group; ties broken by the ORDER BY (add a tiebreak column, or the drop is nondeterministic).
RANK() — every tied row at the cutoff is included, and rank numbers skip after a tie (1, 2, 2, 4).
DENSE_RANK() — ties included, no gaps in the numbering (1, 2, 2, 3).

Ask in English — then read the SQL it ran

This is a good case for asking in plain English and reading the query back, precisely because the failure mode is silent: the wrong-LIMIT version runs fine and returns rows — just the wrong ones — and the tie behaviour never announces itself. "Top 3 products per category by revenue" should hand you back both the ranked rows and the ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) it compiled, so you can confirm it partitioned by the column you meant and see how ties resolve before you trust the numbers in a deck.

(That is the half we built nlqdb for: ask the top-N question in English over a Postgres it provisions, or one you already run via a signed-in connect, and get the ranked rows plus the compiled window-function SQL. Honest split — it returns a read-only ranked answer, not a live leaderboard that updates on its own; and if you want ties included, say so and it switches ROW_NUMBER to RANK or DENSE_RANK.)

The general lesson: when a question says "per" or "in each," the grain moved from the result set to a group inside it, and set-level tools like LIMIT stop applying. Reach for a window function — and decide the tie rule on purpose, because the query will pick one for you whether or not you meant to.

We published 20 blog posts and never shipped a feed. Nothing could subscribe.

Omer Hochman — Sun, 26 Jul 2026 17:32:18 +0000

Originally published at nlqdb.com/blog

For weeks the blog grew and the reach didn't. We published post after post — engineering notes, SQL traps, honest comparisons — and every one rendered fine at its own URL. Anyone who already knew the URL could read it. That was the whole reach: people we'd already reached. The pages were live, and publishing felt done. It wasn't. Publishing a page and publishing a feed are two different acts, and we'd only done the first.

Publishing ends when a machine can subscribe, not when a page renders

A web page is something a human pulls up once. A feed is something a machine subscribes to and pulls forever. Everything that redistributes your writing — every reader, every aggregator, every cross-post integration — is a machine, and a machine needs a stable URL that lists your posts in a format it can parse. That URL is your RSS or Atom feed. Without it, your content has doors a human can walk through one at a time and no door a machine can automate. The blog isn't dead; it's just sealed to everything that would spread it.

What a feed unlocks that a page can't

Feed readers. Feedly, Inoreader, NetNewsWire — the people most likely to follow an engineering blog live in a reader. No feed URL, no way to follow. You are invisible to your most loyal potential audience.
Auto-import to high-authority venues. dev.to, Medium, and Hashnode all have an 'import your posts from RSS' setting: point it at your feed and every new post auto-mirrors to a domain with far more indexing authority than yours — with a rel=canonical link pointing back, so the SEO credit still accrues to your copy. This is the part that actually moves the yield needle, and it is impossible without a feed URL.
Everything else that speaks RSS. Newsletter tools, Slack/Discord post bots, IFTTT/Zapier automations — the long tail of redistribution all keys off one feed URL. Ship it once and every one of these becomes a config field instead of a project.

Without a feed, each of those becomes a manual copy-paste: open the venue, paste the title, paste the body, fix the formatting, set the canonical link by hand. It works for exactly as long as your discipline holds, and then it quietly stops — the third week you're busy, the re-posts don't happen, and nobody notices because there's no error, just silence.

The fix was about 40 lines and no dependency

The obvious move is to reach for a plugin — for us, @astrojs/rss. We didn't. Our posts already live in one typed data file that the sitemap and the llms.txt endpoint both read; a feed is a third reader of the same array. So it's a hand-rolled endpoint that maps each post to an RSS <item>, the exact no-dependency pattern the sitemap already uses, and it runs on Cloudflare Workers with nothing to bundle.

import type { APIRoute } from "astro";
import { BLOG_POSTS } from "../data/blog";

// Titles/descriptions are free text → XML-escape before embedding.
const esc = (s: string) =>
  s.replace(/&/g, "&amp;").replace(/</g, "&lt;").replace(/>/g, "&gt;");

export const GET: APIRoute = () => {
  const items = BLOG_POSTS.map((p) =>
    `<item>\n` +
    `  <title>${esc(p.title)}</title>\n` +
    `  <link>https://nlqdb.com/blog/${p.slug}/</link>\n` +
    `  <pubDate>${new Date(`${p.date}T00:00:00Z`).toUTCString()}</pubDate>\n` +
    `  <description>${esc(p.description)}</description>\n` +
    `</item>`,
  ).join("\n");

  const body = `<?xml version="1.0" encoding="UTF-8"?>\n` +
    `<rss version="2.0"><channel>\n${items}\n</channel></rss>`;

  return new Response(body, {
    headers: { "Content-Type": "application/rss+xml; charset=utf-8" },
  });
};

Two details earn their place. First, XML-escape the free text — titles and descriptions are prose, not known-safe URLs, so an unescaped & or < produces a feed that won't parse (this is the one thing the sitemap endpoint gets to skip, because it only ever emits URL paths). Second, add one <link rel="alternate" type="application/rss+xml"> to the site's <head> so browsers and readers autodiscover the feed from any page — the endpoint exists, but this is what lets a reader find it by pasting your homepage instead of hunting for /rss.xml.

The rule

Count the doors into your content, not the pages. A page count measures how much you wrote; a feed measures how many ways that writing can leave your site without you lifting a finger. A post nobody can subscribe to is a post nobody re-shares — and the gap doesn't show up as an error, it shows up as a referral graph that stays flat while the sitemap keeps growing. If you've been publishing for weeks and the reach isn't compounding, check whether a machine can even subscribe.

(This blog is the daily build log for nlqdb, the data layer you query in plain English. The feed above is real — every post here is auto-importable, rel=canonical back to this domain, from the same typed file that renders the page you're reading.)

You don't need a backend to store form submissions. You need a place to ask "how many."

Omer Hochman — Fri, 24 Jul 2026 22:19:46 +0000

Originally published at nlqdb.com/blog

Every landing page hits the same wall around hour three: the signup form works, but where do the emails actually go? The reflex is to stand up a server and a database for what is, honestly, an INSERT and an occasional COUNT. So most people reach for a form service instead — and that solves storage, but quietly splits your data from your questions. The submissions live in someone else's dashboard; the moment you want "signups per day since launch" or "which referrer actually converted," you're exporting a CSV and pivoting it by hand.

Two problems hiding in one, with different shapes

Capture is a write — a small one, and it genuinely doesn't need a server: an insert call from the page's own fetch, or a ten-line serverless function, is enough, as long as the write key isn't sitting in your client HTML. Reporting is a read, and it's the part that actually wants a database — because "how many per day," "top source this week," and "conversion by campaign" are aggregations, and aggregations want a query planner, not a spreadsheet and a human.

-- capture: one small insert per submission (no server required)
INSERT INTO signups (email, referrer, created_at)
VALUES ($1, $2, now());

-- reporting: the part that actually wants a query planner
SELECT date_trunc('day', created_at) AS day, count(*) AS signups
FROM signups
GROUP BY day
ORDER BY day;

The mistake is picking a tool that's great at the write and leaves you alone with the read. A form service nails capture and hands you a list. A spreadsheet-via-webhook nails capture and hands you a tab you pivot by hand. What you want is for the place the rows land to also be a place you can ask questions of — ideally in plain English, so the day-one question ("did anyone sign up?") and the week-two question ("which tweet drove it?") are the same two-second action, not a data chore.

That's the shape worth looking for, whatever you build it on: storage you can also interrogate. Each submission is a row in a real Postgres, and the reporting question is one English goal — signups grouped by day with a count — that compiles to SQL you can read before you trust it.

That's how nlqdb works, but the point isn't the tool — it's refusing to let your form data land somewhere you can't ask it anything. The honest caveat that applies to any version of this: the public read widget isn't a write endpoint, so capture still goes through a key the browser never sees, and email delivery plus spam filtering stay your front-end's and your ESP's job. Storage isn't the hard part. Not being able to ask your own data a question is.

Your sitemap is advertising redirects — and your canonical tag points at one

Omer Hochman — Tue, 21 Jul 2026 01:31:25 +0000

Originally published at nlqdb.com/blog

Run curl -sI over the URLs in your own sitemap. We did, and 27 of ours answered 307 Temporary Redirect — every route on the site. The canonical tag was worse: each page declared a canonical URL that redirected to the page itself. We were telling crawlers, on every single page, that the authoritative copy lives one hop away.

How a static host quietly forks every URL in two

The mechanics are mundane, which is why this survives review. A static build writes each route as route/index.html. The host serves /route/ directly — and answers the bare /route with a 307 to the slash form. So every route on the site has two URLs: one real, one a redirect. Which one your HTML advertises depends on whoever typed the link.

Our templates typed the bare form everywhere it matters most: the canonical tag, og:url, the sitemap generator, and llms.txt — the four surfaces whose entire job is to name the authoritative URL. All four advertised the redirect.

Why a self-referential redirecting canonical is worth fixing

A canonical pointing at a redirect is a mixed signal. The tag exists to end URL ambiguity; making crawlers resolve a hop to find the 'canonical' copy reintroduces exactly the ambiguity it was meant to close.
Sitemap URLs are supposed to be final. Search engines tolerate redirects there, but every hop is a chance for a URL to get indexed in the form you didn't pick — and split whatever authority the page earns across two spellings.
AI crawlers fetch exactly what you wrote. llms.txt consumers and answer-engine bots are literal; an extra 307 on every fetch is latency you chose, on the surface built for machines.

The fix is a policy plus one helper, not a link hunt

Chasing individual links is the wrong altitude. The durable fix was three moves: pick the slash form as policy (trailingSlash: "always", so the build itself rejects the other spelling); route every emitted URL — canonical, og:url, sitemap, llms.txt — through one path-normalize helper in the head layout and URL generators; and re-audit with curl -sI over every sitemap URL, expecting nothing but 200s.

The one-helper rule is what made the follow-up cheap: once advertised URLs were normalized in one place, sweeping the remaining 1,100+ bare-path hrefs inside page bodies was a mechanical pass, and a link check in the build now fails on any regression. (nlqdb is the database you talk to; this is one of the engineering notes from building it in the open.)

The general lesson: every surface that names a URL — canonical, og:url, sitemap, llms.txt, internal hrefs — should call the same normalize function. If two of them can disagree about your own address, they eventually will, and you'll advertise the disagreement to every crawler that visits.

NOT IN returned zero rows. It wasn't your data — it was one NULL.

Omer Hochman — Mon, 20 Jul 2026 04:24:27 +0000

Originally published at nlqdb.com/blog

"Which customers never placed an order?" is a question you ask constantly — products never sold, users with no login this month, invoices with no payment. It's a set difference, and the obvious query is a quiet trap:

SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);   -- returns nothing. why?

If a single customer_id in that subquery is NULL, you get zero rows — no error, no warning. Here's why: NOT IN (a, b, NULL) expands to id <> a AND id <> b AND id <> NULL. That last comparison is never true — comparing anything to NULL is unknown — so the whole AND chain can never be true, and every row is rejected. One NULL in the inner table silently empties your result.

The two shapes that actually work

-- LEFT JOIN ... IS NULL: keep the customers that found no matching order
SELECT c.* FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- NOT EXISTS: a correlated anti-join, NULL-safe by construction
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Both return the same rows for a plain anti-join. NOT EXISTS stops at the first match and never trips over NULLs. The LEFT JOIN ... IS NULL form is just as correct — but if the join key isn't unique it can multiply rows before the filter, so know your grain. What neither of them does is silently lie to you the way NOT IN does.

The rule worth keeping: reach for NOT EXISTS (or LEFT JOIN ... IS NULL) for "rows with no match," and treat NOT IN (subquery) as a smell unless you're certain the subquery is NULL-free.

If you'd rather not re-derive which shape is safe every time: nlqdb takes "customers who never placed an order" in English, compiles the NULL-safe anti-join, runs it read-only, and shows the SQL so you can confirm it isn't a NOT IN. Honest limit — it owns the Postgres it answers; bring-your-own-Postgres is signed-in only, not the public embed.

Zep gives my agent perfect recall. It still can't answer "average per group" about its own memory.

Omer Hochman — Sun, 19 Jul 2026 07:28:37 +0000

Originally published at nlqdb.com/blog

If you've wired up Zep you know the pitch: it's the Context Lake — a temporal knowledge graph (Graphiti, 27k+ stars) that stores every fact your agent learns as a node with a validity window, resolves entities, and hands back the most relevant facts at query time. For recall it's genuinely good, and it publishes benchmarks (LongMemEval, DMR) to prove it.

But we kept hitting the same wall. Once the agent had logged a few hundred things, we wanted to ask questions about the memory, not retrieve from it:

"Top 10 topics I logged this month, ranked by count."
"Average deal size per stage for enterprise customers."

A knowledge graph has no query planner. It returns relevant facts and hopes the LLM does the arithmetic — which is a hallucination generator, not a GROUP BY.

The honest split (the full side-by-side lives at nlqdb vs Zep): Zep wins on temporal validity, entity resolution, and vector recall over conversation. nlqdb wins when the agent needs to aggregate its memory — it's a real Postgres the agent provisions and queries in English, so GROUP BY / JOIN / HAVING actually work. They compose: Zep the recall layer, nlqdb the analytical store. Pick the one that matches the question you actually need answered.

(Landscape facts verified 2026-06-19; both products' weaknesses are in the comparison, not just ours.)

The NULL timestamp that broke a TTL sweep and a funnel metric at the same time

Omer Hochman — Sat, 18 Jul 2026 10:20:18 +0000

Originally published at nlqdb.com/blog

A row in our databases registry has a last_queried_at column. Two unrelated systems read it: a daily sweep that evicts anonymous DBs whose last_queried_at is older than 90 days, and a funnel metric that counts "DBs that have ever returned an answer." Both quietly broke for the same reason, and the bug is worth sharing because it's a whole class of mistake, not a one-off.

We added the column in a migration that backfilled existing rows (UPDATE … SET last_queried_at = updated_at WHERE last_queried_at IS NULL) — textbook. What we forgot: the INSERT on the create path never set the column. So every row created after the migration was NULL.

Now watch both readers fail, differently:

The sweep silently keeps everything. WHERE last_queried_at < :cutoff looks like it evicts old rows. But in SQL, NULL < anything is NULL, which is not TRUE, so a NULL row never matches a < predicate. The age-based eviction became a no-op for every new row. No error, no log — the table just grows.
The metric silently reads zero. "DBs that returned an answer" was COUNT(*) WHERE last_queried_at IS NOT NULL. Every new row is NULL, so the metric is pinned at 0 regardless of what users actually did. We nearly shipped a "fix" for a conversion problem that didn't exist — the instrument was broken, not the funnel.

Three takeaways

A backfill is not a default. If a column needs a value, set it at write time (a DEFAULT, or in every INSERT). A one-time backfill fixes the past and nothing else.
NULL is not "old" or "zero" — it's "unknown," and it poisons comparisons. Any < / > / != against a nullable column has a third outcome you have to design for. COALESCE at the read, or forbid the NULL.
Before "fixing" a metric that reads 0, prove the instrument can ever read non-zero. Ours structurally couldn't.

(Context: this was in nlqdb, a service that turns plain-English HTML components into SQL — the anonymous-DB sweep is how we keep the free tier's storage bounded. The fix was two lines: seed the column at create, re-run the backfill once.)

The redesign shipped. The smoke test kept walking the old UI.

Omer Hochman — Thu, 16 Jul 2026 09:13:45 +0000

Originally published at nlqdb.com/blog

Our acceptance walkers pin literal UI strings on purpose. A walker asserts the homepage placeholder reads exactly what we ship, that a heading says the words we wrote, that the query composer is where we put it. Drift fails the walk loudly — and a loud failure the moment the surface changes underneath you is precisely the regression detector you want. Silent tolerance is how a broken flow ships green.

Then three things happened in one week. A homepage redesign moved the goal input to a different page. A copy edit reworded a heading. The MCP catalog additively grew two tools. None of these broke the product. All three broke the walkers — which dutifully reported 0/9, and kept reporting it for a week.

The trap isn't the literal assertions

The reflex is to blame the pinned strings and loosen them into fuzzy matches. That's the wrong lesson. The literals did their job: the surface changed, the walk went red. The actual cost is that a red which mixes "the product broke" with "the test went stale" takes a full manual triage to disentangle — and ours contained both at once. Two flows were red from pure test-drift. One flow was red from a real production wall. Same 0/9. You cannot tell which is which from the number, so every red costs you the same expensive human read regardless of whether anything is actually wrong.

Three notes that make pinned walkers pay off

Pinned literals are fine only if reds are triaged inside a bounded window. A detector nobody reads within a day isn't a detector — it's drift accumulating interest until the next person can't tell a week-old copy edit from this-morning's outage.
The failure detail must name the element and the expectation. placeholder was null, expected "Ask your data anything" is decidable from the artifact alone — you know instantly it's drift, not breakage. failed at step 2 forces you to re-run the whole walk by hand to find out.
"A PR touching a walked surface re-runs the walker" was already our rule — and it was skipped, because it was a convention, not a gate. A convention without an enforcing check is a wish. Wire the walker into the surface's required checks, or accept the false-red debt knowingly — but don't pretend a rule nobody enforces is protecting you.

This is a testing-hygiene pattern, not a product feature: it's for anyone whose end-to-end suite asserts real rendered copy rather than test-ids. The literals are worth keeping — they catch the drift you'd otherwise ship. Just make the red self-explaining and triage it on a clock, or the detector quietly becomes a week of noise that hides the one failure that mattered. nlqdb is a database you query in plain English; this is one of the measurement lessons from keeping our stranger-walk honest as the product underneath it moved.

Your LLM fused the two columns you asked for — and the eval marked it wrong

Omer Hochman — Tue, 07 Jul 2026 20:43:35 +0000

You ask a text-to-SQL model to "list the members' names". The benchmark's gold query returns first_name, last_name — two columns. The model returns one: a helpfully assembled full name. Read side by side, the model's answer is arguably the better one. The scorer marks it wrong, every single time, and your engine number reads lower than the engine is.

-- gold: two columns
SELECT first_name, last_name FROM member WHERE ...;

-- model: one column, same information
SELECT first_name || ' ' || last_name FROM member WHERE ...;

Why every execution-accuracy scorer does this

Execution accuracy — the metric behind BIRD, Spider, and most private text-to-SQL evals — runs both queries and compares positional value tuples: canonical BIRD literally compares set(fetchall()). Column names are ignored on purpose (aliases shouldn't matter), which means the shape of each row is all that's left. A one-column result can never equal a two-column gold, no matter how right the content is.

This is the mirror image of the extra-columns failure everyone already knows about — where the model SELECTs a helpful extra field and the added column breaks tuple equality. Fusing two requested columns into one is exactly as fatal as adding one nobody asked for. The scorer has no notion of "close": the tuple matches or it doesn't.

The loss is real, measurable, and lopsided

We bucketed a full 500-question BIRD-dev run of our free-lane engine with a structural differ before touching the prompt. The concatenation signature was unambiguous:

7 of 238 losses concatenated columns where the gold query kept them separate.
0 of 256 wins used || at all — the operator appeared only in losing answers.
Gold itself used || in 1 of 500 questions.

That last line is the decision. When a construct shows up in 3% of your losses, none of your wins, and almost none of the gold answers, discouraging it is near-pure upside — there is essentially nothing on the other side of the trade to regress.

The fix is one sentence in the planner prompt

No fine-tune, no reranker: one projection directive — return each requested attribute as its own column unless the question explicitly asks for a single combined string. Before shipping it we de-concatenated the 7 flagged predictions by hand and re-executed them against the real SQLite databases to get a deterministic ceiling: +3 questions flip wrong-to-right, zero regressions. The live re-measure confirmed the direction: run-wide || concatenations dropped from 7 to 3, and 2 of the 3 ceiling questions flipped to matches.

The general method matters more than this one directive: bucket your loss mass with a structural differ first, compute the deterministic ceiling of a candidate fix second, and only then edit the prompt. Prompt directives written from vibes overfit; directives written from a 7-losses/0-wins histogram are as close to a free lunch as engine work gets.

The rule

The model's job is to match the shape of the answer, not to make it pretty. A helpful concatenation is still a wrong result set — and unless you bucket your losses, you'll never notice that some of your "wrong" answers were the model being more helpful than your gold.

(This directive now ships in nlqdb, the data layer you ask in English. The eval harness that found it — structural mismatch classifier, deterministic-ceiling re-scorer — runs against public BIRD/Spider plus our own ICP-shaped persona benchmark, so prompt changes land with a measured delta, not a hunch.)

Originally published on nlqdb.com.