Nested Subqueries, Self-Joins, or Window Functions?
Early last year, our revenue dashboard started lagging as our order volume hit the 5 million record mark. The breaking point arrived every Monday morning when the marketing team pulled reports on the top 3 highest-value orders alongside comparisons with adjacent purchases. CPU usage on our RDS PostgreSQL instance spiked to 98%, and queries took over 30 seconds to return results.
Facing this bottleneck, backend engineers typically consider three approaches:
- Correlated Subqueries: Stuffing subqueries into
SELECTorWHEREclauses. This is usually the most intuitive approach to conceptualize initially. - Self-Joins: Joining a table with itself using timestamps or IDs.
- Window Functions (The OVER + PARTITION BY Clause): Computing metrics directly across data partitions without collapsing individual rows.
Production Realities: A Comparison After 6 Months of Refactoring
After benchmarking on an orders table holding around 4.2 GB of data, the performance gap became glaringly evident:
1. Subqueries and Self-Joins: Quick to Write, Painful to Run
Junior developers often reach for subqueries because they feel convenient. However, the database pays a steep price:
- I/O Overload: Correlated subqueries force the engine to execute millions of repeated full table scans ($O(N^2)$), rapidly exhausting the buffer cache.
- Maintenance Nightmare: Fetching preceding and succeeding orders required three self-joins. The SQL script spanned an entire page, making it nearly impossible for teammates to parse and maintain.
2. Window Functions: Single-Pass Scan, Plummeting Latency
Once the team migrated the queries entirely to Window Functions, query latency plummeted from 28 seconds down to just 340ms.
- Major Advantage: PostgreSQL scans the table only once (Single-Pass Scan). The working data is gathered into memory (work_mem) and evaluated immediately. The query footprint was cut in half, yielding much cleaner logic.
- Important Caveat: Window Functions are executed after the
WHERE,GROUP BY, andHAVINGstages. As a result, you cannot directly place a window function inside aWHEREclause to filter records. Instead, you need to wrap the computation in a CTE (WITH ... AS) or an outer subquery.
When Should You Use Window Functions?
Here is a quick rule of thumb:
- Need to collapse rows (requiring a single aggregated summary per customer group)? Go with
GROUP BY. - Need to retain individual row details while appending calculated columns (ranking orders, retrieving the previous transaction, computing running totals)? Choose
Window Functions.
Hands-on Practice with PostgreSQL
Let’s set up a test table simulating an order scenario:
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT,
amount NUMERIC(10, 2),
order_date DATE
);
INSERT INTO orders (customer_id, amount, order_date) VALUES
(101, 150.00, '2026-01-05'),
(101, 300.00, '2026-01-12'),
(101, 300.00, '2026-02-01'),
(102, 50.00, '2026-01-10'),
(102, 200.00, '2026-01-18'),
(103, 500.00, '2026-02-15');
If you have an existing CSV dump from a legacy database and need a quick test, you can check out toolcraft.app/en/tools/data/csv-to-json to convert it to JSON or inspect its structure without worrying about data leaking to external servers.
1. Distinguishing ROW_NUMBER, RANK, and DENSE_RANK
These three functions are frequently confused when tackling ranking problems:
ROW_NUMBER(): Assigns sequential numbers (1, 2, 3…) strictly incrementing, regardless of tied values.RANK(): Rows with identical values share the same rank, but subsequent ranks skip numbers (1, 2, 2, 4).DENSE_RANK(): Rows with identical values share the same rank without skipping subsequent numbers (1, 2, 2, 3).
Let’s rank order amounts per customer:
SELECT
order_id,
customer_id,
amount,
ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY amount DESC) AS row_num,
RANK() OVER(PARTITION BY customer_id ORDER BY amount DESC) AS rnk,
DENSE_RANK() OVER(PARTITION BY customer_id ORDER BY amount DESC) AS dense_rnk
FROM orders;
Here, PARTITION BY customer_id segments the orders per customer, while ORDER BY amount DESC sorts orders from highest to lowest within each partition.
2. Retrieving Top N Records per Group
To extract each customer’s most expensive order, we pair a CTE with ROW_NUMBER():
WITH ranked_orders AS (
SELECT
order_id,
customer_id,
amount,
ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY amount DESC) AS rn
FROM orders
)
SELECT customer_id, order_id, amount
FROM ranked_orders
WHERE rn = 1;
3. Calculating Delta Against Preceding/Succeeding Rows with LAG and LEAD
When computing Month-over-Month (MoM) growth or time gaps between purchases, LAG and LEAD provide an exceptionally clean solution:
LAG(column, offset, default): Retrieves values from a preceding row.LEAD(column, offset, default): Retrieves values from a succeeding row.
Calculate revenue variance compared to a customer’s immediately preceding order:
SELECT
customer_id,
order_date,
amount AS current_amount,
LAG(amount, 1, 0) OVER(PARTITION BY customer_id ORDER BY order_date) AS prev_amount,
amount - LAG(amount, 1, amount) OVER(PARTITION BY customer_id ORDER BY order_date) AS diff_amount
FROM orders;
4. Computing Running Totals
Instead of writing complex recursive queries, simply combine SUM() with an ORDER BY clause:
SELECT
customer_id,
order_date,
amount,
SUM(amount) OVER(
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders;
Each row automatically accumulates totals from the initial transaction up to the current entry. The syntax is concise and efficiently leverages composite indexes on (customer_id, order_date) without triggering database I/O bottlenecks.
