Articles
SQL LATERAL and APPLY Interview Questions: Top-N per Row and Missing Matches
Practice SQL LATERAL and APPLY interview questions with per-row Top-N, eligibility filters, stable ties, missing matches, and window alternatives.
SQL LATERAL and APPLY interview questions become difficult when “the latest two orders” means the latest two eligible orders for each customer, including customers who have none. A global limit, an unstable tie, or a predicate in the wrong place can produce plausible results while answering a different question.
This guide uses official PostgreSQL and SQL Server documentation for syntax and semantics. The dataset, counterexamples, and reasoning exercises are original preparation material, not candidate reports of an employer's assessment.
What problem does a lateral relationship solve?
A lateral item can refer to columns supplied by preceding items in the FROM clause. PostgreSQL describes evaluating the lateral item for each relevant outer row or row set and joining its result. That gives you a relational way to express “use this customer's identifier to select that customer's orders.” PostgreSQL table expressions
The output cardinality matters. The right side may produce zero, one, or several rows for one customer. It is not simply a scalar subquery that must fit a single value. The join form determines what happens when the right side is empty.
SQL Server expresses a related per-left-row relationship with APPLY. CROSS APPLY removes a left row when the right side is empty; OUTER APPLY preserves it with null right-side columns. Use the syntax of the named database rather than mixing LIMIT, TOP, and LATERAL in one answer. SQL Server FROM and APPLY
This is a semantic explanation, not a promise that the optimizer literally executes the same subquery repeatedly in every plan. Describe the required result first, then inspect the actual execution plan.
Can you solve a fixture that exposes three common errors?
The original exercise asks for each customer's two most recent paid orders. Retain customers with no paid orders. Break timestamp ties by larger order ID. These choices belong to the requirement, not to an accidental storage order.
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
created_at TIMESTAMP NOT NULL,
status VARCHAR(20) NOT NULL
);
INSERT INTO customers VALUES (1), (2), (3);
INSERT INTO orders VALUES
(11, 1, '2026-09-01 10:00:00', 'paid'),
(12, 1, '2026-09-01 10:00:00', 'paid'),
(13, 1, '2026-09-02 10:00:00', 'cancelled'),
(21, 2, '2026-09-01 09:00:00', 'paid');
A PostgreSQL solution is:
SELECT c.customer_id, recent.order_id, recent.created_at
FROM customers AS c
LEFT JOIN LATERAL (
SELECT o.order_id, o.created_at
FROM orders AS o
WHERE o.customer_id = c.customer_id
AND o.status = 'paid'
ORDER BY o.created_at DESC, o.order_id DESC
LIMIT 2
) AS recent ON TRUE
ORDER BY c.customer_id, recent.created_at DESC,
recent.order_id DESC;
The expected ordered customer/order pairs are (1,12), (1,11), (2,21), and (3,NULL). Customer 1's newest order is cancelled, so it must not consume a paid-order slot. Customer 3 has no orders, so its preserved row proves the outer relationship works.
ON TRUE joins all rows already selected by the correlated right side. The correlation and eligibility predicate are inside that subquery. Moving the customer predicate out while retaining the limit can select the wrong global candidates before the join.
We ran the fixture and the lateral/window comparison in DuckDB 1.5.2, which supports this query shape. The saved checks cover result semantics and counterexamples. They are not PostgreSQL or SQL Server execution-plan tests; the dialect-specific contracts come from those products' documentation.
How do you write the SQL Server version?
Use OUTER APPLY and TOP (2) for the same stated row-count contract:
SELECT c.customer_id, recent.order_id, recent.created_at
FROM customers AS c
OUTER APPLY (
SELECT TOP (2) o.order_id, o.created_at
FROM orders AS o
WHERE o.customer_id = c.customer_id
AND o.status = 'paid'
ORDER BY o.created_at DESC, o.order_id DESC
) AS recent
ORDER BY c.customer_id, recent.created_at DESC,
recent.order_id DESC;
The inner ordering chooses the two candidates. The outer ordering controls how the final combined rows are displayed. Do not assume that an ordered subquery establishes the final result order without an outer ORDER BY.
Microsoft recommends using ORDER BY with TOP to predict which rows are affected. WITH TIES is a different contract: it can return more than the requested count when the ordering values tie. SQL Server TOP
If the requirement is “all orders at the second distinct timestamp,” an ID tie-breaker changes the peer definition. Clarify that requirement and use an appropriate rank or cutoff calculation. A deterministic exactly-two-row answer is not interchangeable with an all-ties answer.
Where must eligibility filters live?
Filter the candidates before selecting their top N. In the fixture, selecting the latest two orders first yields IDs 13 and 12 for customer 1. Filtering out cancelled orders afterward leaves only 12, even though another eligible paid order exists.
That is a different operation from selecting the latest two paid orders, which yields 12 and 11. State the sequence as eligibility, ranking, selection. It helps a reviewer locate mistakes hidden inside nested SQL.
Now put a right-side predicate in the outer WHERE, such as WHERE recent.order_id IS NOT NULL. Customer 3 disappears because its preserved null row fails that predicate. The result may be appropriate for “only customers with qualifying orders,” but it no longer meets the fixture's requirement.
These two errors are independent. The first changes which child rows qualify for the slots. The second changes whether an unmatched parent survives. Repairing one does not repair the other.
Why is a global LIMIT not top N per row?
A limit outside the joined result restricts the whole output. It does not restart for each customer. In this fixture, a final LIMIT 2 can return only customer 1's two rows and omit both customer 2 and the unmatched customer 3.
An uncorrelated right-side subquery with LIMIT 2 also selects one global candidate set. Joining that set to customers cannot recover orders excluded before the join. The word per in the requirement should appear as a correlation key or partition key in the query.
Ask a follow-up with one customer having thousands of recent orders and another having one older order. That makes a global selection particularly easy to expose. A dataset in which everyone has exactly one order will not distinguish many incorrect implementations.
Also name the true grouping key. If customer IDs repeat across tenants, correlate both tenant_id and customer_id, and carry those keys through any partition and join. Correct top-N logic on an incomplete key can mix another tenant's records into the result.
Is ROW_NUMBER an equivalent alternative?
For a fixed row limit and deterministic ordering, a prefiltered window query can express the same result:
WITH ranked AS (
SELECT o.*,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY created_at DESC, order_id DESC
) AS rn
FROM orders AS o
WHERE status = 'paid'
)
SELECT c.customer_id, r.order_id, r.created_at
FROM customers AS c
LEFT JOIN ranked AS r
ON r.customer_id = c.customer_id
AND r.rn <= 2
ORDER BY c.customer_id, r.created_at DESC, r.order_id DESC;
The rank predicate remains in the join condition so unmatched customers survive. Moving r.rn <= 2 into WHERE excludes the null-extended row. Prefiltering paid orders also ensures the rank counts only eligible candidates.
PostgreSQL explains that window functions operate on the rows remaining after the earlier query filtering stages. It also distinguishes the window's ordering from final output ordering. Those facts support the placement above. PostgreSQL window functions
Do not answer “windows are always faster” or “lateral always uses an index.” If the request needs only a small set of customers, selective per-customer access may help. If it needs most customers, ranking a broad eligible set may be competitive. These are planning hypotheses to test with representative data.
What if each customer has a different quota?
A lateral query is useful when the right-side selection depends on more than an identifier. An original extension gives each customer a recent_limit and applies that value to its selection. PostgreSQL can use an outer value in the lateral limit expression; adapt syntax deliberately for other engines.
Define invalid and missing quotas before writing the query. Does zero mean retain the customer with no child rows? Does null mean no limit, a configured default, or an invalid request? PostgreSQL treats LIMIT NULL as no limit, so silently passing a missing quota can violate a bounded-result requirement. PostgreSQL LIMIT and OFFSET
For a service endpoint, validate and cap the requested count according to its contract. Then reason about the total output bound: a per-parent cap of N does not cap the number of parents. Returning many parents with their own child allowance can still produce a large response.
This extension demonstrates why lateral is more than another spelling of ranking. It can express a dependent relation with parent-specific parameters. The additional flexibility also creates more boundary cases to define and test.
What performance evidence should accompany the answer?
Start with a candidate index matching the access pattern: customer key, eligibility where useful, and descending selection keys. For PostgreSQL, a partial index restricted to paid orders is another design to investigate when that predicate is stable and applicable. PostgreSQL partial indexes The exact choice depends on selectivity, write cost, and the actual query.
PostgreSQL's multicolumn-index documentation explains how leading columns affect B-tree scan restrictions. An index beginning with the correlation key can support a narrower lookup, but its presence alone does not prove that the chosen plan avoids large scans or sorts. PostgreSQL multicolumn indexes
Inspect the plan with realistic customer counts, skew, eligible-order density, and output sizes. For the lateral approach, examine repeated work and rows visited before enough eligible matches are found. For the window approach, examine the eligible set being ranked and any sorting cost.
Finally, state the consistency requirement. The query defines a result for the data visible to its statement or transaction under the chosen engine's isolation behavior. If orders can change between separate page requests, stable ordering alone does not guarantee a stable multi-request snapshot. Avoid solving that separate requirement with an unsupported claim about lateral joins.