Efficient Percentile Ranking in PostgreSQL

When analyzing data, percentiles help us understand the relative ranking of records within a dataset. In PostgreSQL, we can compute percentiles efficiently using the PERCENT_RANK() OVER window function.

Understanding Percentiles

The PERCENT_RANK() function calculates the relative rank of a row within a partitioned dataset. The formula used internally is:

This results in values ranging from 0.0 (for the lowest-ranked row) to 1.0 (for the highest-ranked row), providing a useful measure of distribution.

Example Data Structure

Let's assume we have a simple posts table:

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT NOT NULL
);

And for each post we can have multiple comments:

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INT NOT NULL,
    comment_text TEXT NOT NULL
);

And we have visits table, where we keep track of each post visits:

CREATE TABLE visits (
    id SERIAL PRIMARY KEY,
    post_id INT NOT NULL,
    visit_time TIMESTAMP DEFAULT now()
);

The goal is to find out each post performance based on the number of visits/comments compared to all other posts.

Querying Percentiles

For that we can calculate two separate percentiles for each post:

1. Based on the number of comments
2. Based on the number of visits

Visits Percentile

First, we need to calculate number of visits for each post:

SELECT post_id, COUNT(*) AS visit_count
    FROM visits
    GROUP BY post_id

Then we join it with the posts:

SELECT 
    p.id, 
    p.title,
    COALESCE(vc.visit_count, 0) AS visit_count,
    PERCENT_RANK() OVER (ORDER BY COALESCE(vc.visit_count, 0)) AS visit_percentile
FROM posts p
LEFT JOIN (
SELECT post_id, COUNT(*) AS visit_count
    FROM visits
    GROUP BY post_id
) vc ON p.id = vc.post_id

A few things to note here:

1. We use LEFT JOIN because some posts might not have visits yet, but we want them to be present in our performance report.
2. For the same reason we use COALESCE(vc.visit_count, 0) instead of vc.visit_count - we want zeros instead of NULL there.

The line which calculates the percentile:

PERCENT_RANK() OVER (ORDER BY COALESCE(vc.visit_count, 0)) AS visit_percentile

Comments Percentile

To calculate percentile based on the number of comments follows the same pattern: calculate number of comments for each post, join them with posts, and use window function PERCENT_RANK() to get the percentiles.

SELECT post_id, COUNT(*) AS comment_count
    FROM comments
    GROUP BY post_id

We'll omit the intermediate query and show the final version which returns posts with both percentiles:

SELECT 
    p.id, 
    p.title,
    COALESCE(vc.visit_count, 0) AS visit_count,
    COALESCE(cc.comment_count, 0) AS comment_count,
    PERCENT_RANK() OVER (ORDER BY COALESCE(vc.visit_count, 0)) AS visit_percentile,
    PERCENT_RANK() OVER (ORDER BY COALESCE(cc.comment_count, 0)) AS comment_percentile,
FROM posts p
LEFT JOIN (
SELECT post_id, COUNT(*) AS visit_count
    FROM visits
    GROUP BY post_id
) vc ON p.id = vc.post_id
LEFT JOIN (
SELECT post_id, COUNT(*) AS comment_count
    FROM comments
    GROUP BY post_id
) cc ON p.id = cc.post_id

Conclusion

By leveraging PERCENT_RANK(), we can easily determine how each post ranks relative to others based on different metrics. This approach is highly efficient and useful for analytics in PostgreSQL.