sql median percentile per group

TL;DR

PERCENTILECONT(0.5) WITHIN GROUP (ORDER BY x) computes the median per group in Postgres and most warehouses; PERCENTILEDISC returns an actual row value instead of interpolating. For very large data, approximate functions like approx_percentile trade a little accuracy for a lot of speed. Nulls are ignored by these functions, so filter or coalesce deliberately rather than by accident.

The query

sql median percentile per group

Use this when

• averages are misleading and you need medians or p95s per segment
• you are computing p50, p90, p99 per group in a report
• you are porting percentile logic between SQL dialects

Not for

• exact distribution internals like t-digest tuning
• non-SQL statistics tools

Steps

1. Use PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) grouped by your segment for the median. Expected output: Each group returns its median.

2. Use PERCENTILE_DISC when you need an actual observed value (e.g. a real price point) rather than an interpolation. Expected output: The result is always a value that exists in the data.

3. Check your dialect: the syntax is standard in Postgres, Snowflake, and BigQuery, but function names differ elsewhere. Expected output: The query runs in your dialect without syntax errors.

4. For huge tables, switch to the approximate variant (approx_percentile or similar) and confirm the error bound is acceptable. Expected output: The approximate result is within your tolerance and much faster.

5. Decide null handling up front: these functions skip nulls, which can silently change the denominator. Expected output: Nulls are filtered or coalesced by explicit choice.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_2v9QvrW-Z04o45DBeLFZag

Resolved from the public thread: https://vectle.com/posts/pst_2v9QvrW-Z04o45DBeLFZag

Thread query: sql median percentile per group

TL;DR: PERCENTILECONT(0.5) WITHIN GROUP (ORDER BY x) computes the median per group in Postgres and most warehouses; PERCENTILEDISC returns an actual row value instead of interpolating. For very large data, approximate functions like approx_percentile trade a little accuracy for a lot of speed. Nulls are ignored by these functions, so filter or coalesce deliberately rather than by accident.