Percentile

Hook: Percentiles are how you catch the tail of the data — the slow requests, the top salaries, the worst delays — which is why interviewers love them when they want to see if you can think beyond averages.

Question: Percentile

Answer: A percentile is a cutoff value in sorted data. For example, the 90th percentile is the value at or below which 90% of the rows fall. In SQL, you usually calculate it with PERCENTILE_CONT for an interpolated value or PERCENTILE_DISC for one of the actual stored values, and many engines apply it per group or partition.

Interview-Ready Answer: I would say a percentile is a threshold in ordered data: p50 is the median, and p95 means 95% of the values are at or below that point. In SQL, I use PERCENTILE_CONT when I want a mathematically interpolated cutoff and PERCENTILE_DISC when I need a real row value from the table. The key detail is that percentiles describe the tail of the distribution, so they are much more useful than averages for things like latency and salaries.

What a percentile means

A percentile is a fence line in ordered data. If a metric is noisy, the average can hide pain, but the percentile shows where the bad tail starts. In interview terms, a window function is a function that can look across a set of rows without collapsing them into one row; percentile logic is often used across a whole group, or across a partition like one service, one region, or one day.

How it works under the hood

1. SQL first removes NULL values from the input set, because a missing value is not part of the distribution.
2. It sorts the remaining values by the requested column inside each group or partition.
3. For PERCENTILE_CONT(p), the engine finds the target position using the fraction p between 0 and 1. A common mental model is 1 + p * (n - 1), where n is the number of rows.
4. If that target lands exactly on a row, the function returns that value. If it lands between two rows, PERCENTILE_CONT interpolates, meaning it blends the two neighboring values proportionally.
5. For PERCENTILE_DISC(p), the engine does not invent a new value. It returns the first actual row value whose cumulative share is at least p.
6. The same logic is then repeated for every partition or group, so you get one percentile per department, service, customer segment, or whatever you grouped by.

Example: with sorted values 10, 20, 30, 40, 100, the 90th percentile with PERCENTILE_CONT lands between the 4th and 5th values. It interpolates to 76, while PERCENTILE_DISC returns 100, because that is the first actual value that reaches the 90% cutoff.

Exact percentile vs bucketed ranking

That table is the interview trap: NTILE(10) makes deciles, but it is not the same thing as the 90th percentile value. Percentiles answer, “What value marks the boundary?” Ranking functions answer, “Where does this row sit?”

Syntax differences by database

That difference matters in interviews. The idea is the same, but the exact SQL varies by dialect, so always name the engine or explain the syntax you are using.

Performance and edge cases

• Percentiles usually require a sort, so think O(n log n) time for each partition. Memory is often O(n) for the active partition, and large sorts can spill to disk.
• On PostgreSQL, if a sort exceeds work_mem, the engine may spill to temporary files; on a wide multi-million-row partition, that can turn a fast query into a slow one.
• There is no default percentile value. You must choose p, and it must be between 0 and 1. 0.5 is the median.
• If all values in a group are NULL, the result is NULL, not an error.
• For even row counts, PERCENTILE_CONT may return a blended number, while PERCENTILE_DISC returns one actual row value; that is a common source of confusion.

Memory model: think of percentile as drawing a line across a sorted crowd. PERCENTILE_CONT lets the line cut between two people; PERCENTILE_DISC snaps to the next real person standing on the line.

Real-World Example: Imagine a checkout service in an e-commerce app. The team tracks p50 and p95 latency per region every day, because the average response time can look fine while a small group of users still sees painful delays. A query like this powers the dashboard that tells the on-call engineer whether the service is healthy or quietly getting worse.

What goes wrong when someone misunderstands percentile? A developer replaces p95 with AVG(latency) in the reporting query. The dashboard stays green at 180 ms, but customers in one region are actually hitting 2.4 second checkout delays because a few slow calls are buried in the average. The symptoms are confusing: support tickets spike, retry logs increase, and the alert never fires because the wrong metric was wired into the threshold. Once the team switches back to p95, the tail becomes visible and the incident is easy to explain.

Follow-up & Tricky Questions:

• How do you calculate a percentile per department? Add PARTITION BY department in a window-capable dialect, or GROUP BY department with WITHIN GROUP in PostgreSQL-style syntax. The idea is the same: compute one cutoff per group, not one global cutoff.
• What is the difference between PERCENTILE_CONT and PERCENTILE_DISC? CONT can interpolate and return a value that is not in the table; DISC always returns an actual row value. Use continuous for numeric measurements and discrete for categories or when the exact stored value matters.
• How do NULL values affect percentiles? They are ignored by the aggregate in the common implementations. If every value in the partition is NULL, the result is NULL.
• Can I compute multiple percentiles at once? Yes, many engines let you ask for several cutoffs in one query, which is usually better than running separate queries and sorting the same data repeatedly. The exact syntax varies, so check your dialect.
• When would you prefer percentiles over averages? Whenever the tail matters: latency, delivery time, incident duration, salary bands, fraud scores, or any metric where a small set of bad values changes the user experience.
• Is NTILE(100) the same as percentile? No. It creates 100 buckets of rows, but the bucket boundary is not the same as the mathematical 90th or 95th percentile value. It is an approximation tool, not an exact percentile function.
• Does PERCENTILE_CONT always return a row from the table? No. If the target falls between two rows, it returns an interpolated value, which can be new and not present in the source data.
• Is the median always the middle row? Only for odd counts and only if you are using a discrete definition. With continuous percentile logic and an even count, the median is often the average of the two middle values.

Common Mistakes:

• Using AVG instead of p95. Fix: averages hide bad tails; use percentile when the slowest users matter.
• Thinking NTILE returns a percentile value. Fix: it returns a bucket number, so it is only a rough grouping tool.
• Forgetting the dialect difference. Fix: SQL Server often uses analytic syntax with OVER, while PostgreSQL uses WITHIN GROUP plus GROUP BY.
• Ignoring NULL behavior. Fix: percentiles usually ignore NULL, and all-null groups yield NULL.

Memory Hook: Think of percentile as the line at a concert entrance: p90 means 90% of the crowd is already inside, and only the slow tail is still outside the gate.

Cheat Sheet:

• p50 = median.
• PERCENTILE_CONT = interpolated cutoff.
• PERCENTILE_DISC = actual stored value.
• NTILE = bucket number, not a cutoff.
• Percentiles are great for skewed data.
• Expect a sort, so large partitions can be expensive.

Practice Tasks:

• Compute p50 and p95 response times per API endpoint from a sample table.
• Compare PERCENTILE_CONT(0.5) and PERCENTILE_DISC(0.5) on a table with an even number of rows.
• Replace a dashboard’s average with a percentile and explain how the story changes.