nlqdb

Solve · Analysts and PMs

How do I calculate a median or percentile in SQL?

If you need a median or a percentile — the middle value, or the p90 of response times — ask in plain English instead of remembering Postgres has no `MEDIAN` function. nlqdb compiles the ordered-set aggregate `PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value)`, runs it in Postgres, and shows the SQL so you can check the order.

Analysts, PMs, and ops leads reach for the median when the mean lies — one whale order or a stalled request drags the average away from the typical row, so you want the middle value or a p90/p95 tail instead. But Postgres has no `MEDIAN()` function the way it has `AVG()`, and this is the trap: median is an ordered-set aggregate, `PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value)`, with a `WITHIN GROUP (ORDER BY ...)` clause that looks nothing like a normal aggregate. It gets re-Googled every time, and the `PERCENTILE_CONT` (interpolate between the two middle rows) vs. `PERCENTILE_DISC` (return an actual row from the set) choice is exactly where two people get two different 'medians' from the same data.

The snippet that solves it.

> the median and 90th-percentile revenue per order

What nlqdb does for this

  • Ask 'median revenue per order'; nlqdb compiles the ordered-set aggregate (`PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY revenue)`) and runs it in Postgres.
  • Every answer returns the value plus the compiled SQL under a trace toggle (`SK-WEB-005`) — verify the order and whether it used `PERCENTILE_CONT` or `PERCENTILE_DISC`.
  • Works no-code over a provisioned demo, or connect a Postgres you already run (BYO connect, `SK-DBCONN-001`) to summarise your real rows.
  • Repeated percentile summaries hit the plan cache — content-addressed on `(goal-fingerprint, schema-hash)` (`GLOBAL-006`) — so the same statistic returns in single-digit ms.

Drop into any HTML page

<nlq-data goal="the median and 90th-percentile revenue per order"></nlq-data>

The middle-value-and-tail summary you'd otherwise hand-write with an ordered-set aggregate and the right `WITHIN GROUP` clause is one English goal here, with the SQL shown so you can check the order and whether it interpolated.

What this replaces

What nlqdb doesn't try to do here

  • The median needs a column it can order — a number, date, or duration. Over values with no meaningful sort, or a mix nlqdb can't rank, the 'middle' isn't defined and it won't invent one.
  • Continuous vs. discrete is a real choice: `PERCENTILE_CONT` interpolates between the two middle rows (median of [1, 2] is 1.5), `PERCENTILE_DISC` returns an actual value from the set (1). nlqdb picks one and shows it in the SQL — it doesn't guess which you meant when you don't say.
  • nlqdb returns the percentile with a read-only SELECT — it's not a BI tool maintaining a live p95-latency chart or refreshing the summary on a schedule. That's a scheduled job's work.
  • The public `<nlq-data>` embed is read-scoped — it summarises existing rows, it doesn't write. No write key belongs in client HTML; loading the data goes through the SDK or `POST /v1/run`.

Questions buyers ask

How do I calculate a median in SQL?
Ask in plain English — 'median revenue per order.' Postgres has no `MEDIAN()` function, so nlqdb compiles the ordered-set aggregate `PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY revenue)`, runs it, and returns the value plus the SQL it ran. You get the middle value without remembering the `WITHIN GROUP` syntax. The honest limit: you have to name a column it can order by.
What's the difference between PERCENTILE_CONT and PERCENTILE_DISC?
`PERCENTILE_CONT` interpolates between the two middle rows — the continuous median of [1, 2] is 1.5. `PERCENTILE_DISC` returns an actual value present in the data — the discrete median of [1, 2] is 1. They diverge on even-sized sets and on categorical-but-ordered data. nlqdb shows which one the compiled SQL used so you can confirm you got the one you meant.
Can I get a p90, p95, or p99 percentile too?
Yes — they're the same ordered-set aggregate with a different fraction: `PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY latency)` for p90, `0.95` for p95. Ask for 'the 95th percentile of response time' and nlqdb compiles the fraction, then shows the SQL. The honest limit: name the column and the percentile you want.
Can I compute a median or percentile on a Postgres database I already run?
Yes — connect it with the signed-in BYO connect verb (`nlq db connect`, `SK-DBCONN-001`; see /solve/query-existing-postgres-in-natural-language) and ask for the summary in place, no ETL into a separate store. The honest limits: BYO connect is signed-in only (not the public embed), and nlqdb returns the percentile with a read-only query — it doesn't persist a materialized summary for you.

Where this pain shows up in public

Enduring discussion hubs where you can verify the theme without taking our word for it. We don't quote individual posts; we cite search-result and subreddit URLs that stay live as new threads land.

Try nlqdb in 30 seconds

No sign-in. The anonymous database lasts 72 hours; adopt it with one click if you keep it.

Start with a goal →

Looking at this from a different angle? Browse all solve pages or browse competitor comparisons.