IntermediateSQL · 25 min

Self-Join: Intermediate

You can write a self-join from memory. The interviewer knows that. What they are testing now is whether you understand the tradeoffs between a self-join and the alternatives, whether you can spot the variants of the question that hide behind unfamiliar prompts, and whether you handle the cardinality math before the query runs. Self-joins are a small SQL shape with large operational consequences: pick the wrong inequality and you double your row count; pick the wrong approach and your query fans out by an order of magnitude. This lesson is about navigating those decisions before the bug ships.

Choose between self-join, window function, and recursive CTE based on the comparison shape

Anticipate the cardinality math before writing SQL and reason about the row count out loud

Avoid the fan-out bug when self-joining tables at mismatched grains

Articulate the tradeoff one sentence ahead of the interviewer's follow-up

Directional Pairs and Double-Counting

Daily Life

Interviews

Recognize that questions comparing pairs within a table (manager/employee, previous/next, duplicate detection) require self-joins.

Here is the shape that recurs in interviews past the basics: 'find pairs of customers who placed orders within ten minutes of each other.' The pattern is a self-join. The complication is the inequality, the time-window, and the deduplication. Each of those is a decision point. The candidate who pauses to name those decisions out loud before writing SQL has already cleared the bar. The candidate who writes a fast first version and discovers the duplicate-pair bug while tracing the output spends the rest of the interview defending it.

You are being tested on a self-join at this level when you hear:

▸"find pairs of X that ..."
▸"detect rows that share a value with another row"
▸"compare each row to others in the same group"
▸"events that happened within N minutes of another event"
▸"find the parent or predecessor of each row"
▸Any cross-row comparison within a single table

The decision the interviewer is silently scoring

Self-join is one of three tools for cross-row comparison. The others are window functions and recursive CTEs. Picking the right one for the shape of the comparison is the move. Defaulting to one without articulating why is the yellow flag. The interviewer is checking whether you treat the choice as a tradeoff or as a reflex. At Stripe in late 2022, a fraud detection query that should have been a sliding window comparison was written as an unconstrained self-join on transactions. The query ran fine on the staging dataset (a few million rows) and then crushed the production warehouse on its first scheduled run (two billion rows × two billion rows = unbounded shuffle, OOM'd every executor). The author was a strong engineer; the lesson the team wrote in the postmortem was 'name the cardinality of the join before writing SQL.' Drop that name and that lesson when the interviewer asks about scale; the specificity reads as someone who has been in the room when this kind of bug shipped.

•Reach for a window function

Comparison is to a fixed row offset in a sorted partition (previous row, next row, N rows back)
The output keeps one row per input row
The engine can compute the comparison without materializing the join
The query reads top-to-bottom as a single pipeline

✓Reach for a self-join

Comparison is on a join predicate, not a row position
The output produces pairs or expands per match
Multi-column conditions or range-based comparisons are required
The engine plans the join more predictably than the optimizer rewrites a correlated subquery

What the interviewer is actually testing

Past the basic shape, the interviewer is checking three things. First: do you anticipate the cardinality math? A self-join on a table with N rows can produce up to N² output rows; saying 'this could fan out to a billion rows if I do not constrain the join' before writing SQL is the move. Second: do you pick between LAG and self-join based on the comparison shape, not on which tool you remember? Third: do you avoid the duplicate-pair bug when the comparison is symmetric? Hitting all three is what flips this question from a hire to a strong-hire signal.

The three signals the interviewer is scoring:

▸Cardinality anticipation: name N² before it ships
▸Tool choice: LAG for adjacent rows, self-join for predicate matches
▸Deduplication: < inequality on a unique column for symmetric pairs

Before you write the self-join, name the cardinality out loud. 'If both sides are unconstrained, this produces up to N times N rows. The join condition has to bound the cross-product to something tractable.' That sentence is the move; it tells the interviewer you treat join cardinality as a first-class concern, not as something the engine will figure out.

Self-Join vs Window Function

Daily Life

Interviews

Write a self-join with clear aliases and a precise ON clause that avoids Cartesian explosions.

At this level, the aliasing and join syntax is the floor. The interesting decisions are in how you constrain the join: the predicate that determines which pairs survive, the inequality that deduplicates symmetric pairs, and the additional conditions that bound the time or value window. Each of those is a deliberate choice; sloppy choices ship as performance bugs or wrong row counts.

The canonical self-join with all three constraints

	/* Find pairs of orders by the same customer placed within 10 minutes */
	SELECT
	o1.customer_id,
	o1.order_id AS earlier_order,
	o2.order_id AS later_order,
	o2.order_time - o1.order_time AS gap
	FROM orders AS o1
	INNER JOIN orders AS o2
	ON o2.customer_id = o1.customer_id /* bound to same customer */
	AND o2.order_time > o1.order_time /* directional (no self-pair, no double-count) */
	AND o2.order_time <= o1.order_time + INTERVAL '10 minutes' /* bound the window */
	ORDER BY o1.customer_id, o1.order_time

Three constraints in the ON clause. The first bounds the join to pairs that share a customer; without it, every pair of orders across the entire table is a candidate. The second is the directional inequality (o2.order_time > o1.order_time); it eliminates self-pairs and ensures each unordered pair appears only once. The third is the upper bound on the time window; it converts a potentially N² join into one bounded by the local cardinality of the time window.

the equality on the shared key (customer_id, region) makes the join meaningful, not a global cross-product.
the inequality (o2.order_time > o1.order_time) eliminates self-pairs and ensures each pair appears exactly once.
the upper bound (within ten minutes, same calendar day) converts a potential N² join into one bounded by local cardinality.

Why the inequality is in the ON clause, not the WHERE

Putting the inequality in the ON clause lets the engine apply it during the join, not after. The engine can use the time-window predicate to prune candidate matches before producing the cross-product. Moving it to a WHERE clause produces the same result but is harder for the optimizer to plan efficiently; the engine may materialize the unconstrained cross-product first and filter afterward. Mention this when the interviewer asks about performance; it is a small move with measurable impact on large tables.

•Constraint placement that hurts performance

Inequalities and range conditions in the WHERE clause
Engine may materialize the unbounded cross-product first
Filter applies after the join produces all candidate rows
Plan is harder to optimize on engines without good predicate pushdown

✓Constraint placement that helps the optimizer

All join-related conditions in the ON clause
Engine prunes during the join, not after
Range conditions can use indexes on the join column
Plan is portable across engines

Multi-column join conditions

When the comparison spans multiple columns, the ON clause grows. 'Find customers who placed two orders in different regions within an hour' joins on customer_id, with a directional time inequality, a region inequality, and a time window. Each additional condition is a constraint that the optimizer can use to prune the candidate set. Naming each constraint and what it does is the narrative the interviewer is reading you for.

State each constraint's purpose out loud while you type. 'customer_id equality to bind the pair to one customer. Time inequality to deduplicate and order. Time window to bound the cross-product. Region inequality to filter to cross-region pairs.' Each sentence is a small move; collectively they tell the interviewer you treat the ON clause as the place where the query's correctness is decided.

Avoiding the (a,b)/(b,a) Duplicate

Daily Life

Interviews

Prevent duplicate pairs using a < condition (a.id < b.id) and explain why to the interviewer.

Symmetric pair questions ('find customers in the same region as another customer') are where the duplicate-pair bug ships most often. The naive query returns each pair twice plus every self-pair, inflating the row count by 2N + N². The fix is one inequality, but the choice of which column to use as the tiebreaker has implications for correctness, cardinality, and deduplication semantics.

The bug and the standard fix

Joining customers to themselves on city alone produces (Alice, Alice), (Alice, Bob), (Bob, Alice), (Bob, Bob), and so on. The self-pairs are nonsense. The (Alice, Bob) and (Bob, Alice) duplicates are the same logical pair counted twice. Adding c1.customer_id < c2.customer_id to the ON clause fixes both at once: strict inequality drops self-pairs, and the ordering keeps each unordered pair exactly once.

What the < inequality does in one move:

▸Drops every self-pair (the inequality is strict)
▸Keeps each unordered pair exactly once
▸Generalizes to triples (chain c1.id < c2.id < c3.id) and higher
▸Requires a unique column; non-unique columns produce wrong dedup

	/* Pairs of customers in the same city, deduplicated */
	SELECT
	c1.name AS customer_a,
	c2.name AS customer_b,
	c1.city
	FROM customers AS c1
	INNER JOIN customers AS c2
	ON c1.city = c2.city
	AND c1.customer_id < c2.customer_id

Choosing the column and the direction

Two decisions sit underneath the inequality. First: the column. The inequality column must be unique per row. customer_id is the standard choice because it is the table's primary key; order_id, transaction_id, email, anything-with-a-uniqueness-constraint works. Using a non-unique column for the inequality breaks the deduplication: if two customers have the same name, the < comparison may treat them as equal and drop one of the legitimate pairs, or include duplicates if the values happen to collide. The interviewer at this level will sometimes hand you a table where the obvious column is not unique; the move is to spot it and pick a column that is. Second: the symmetry. Not every cross-row comparison is symmetric. 'Find customer pairs where customer A referred customer B' is directional: (Alice, Bob) is meaningfully different from (Bob, Alice). For directional comparisons, the inequality is wrong; the join condition should encode the direction (referrer.customer_id = referred.referred_by) without the <. The interviewer will sometimes test this by asking a symmetric-looking question that turns out to be directional. Ask 'is the pair ordered or unordered?' before defaulting to the < inequality.

•Symmetric pairs (use <)

Pairs of customers in the same city
Pairs of orders placed within ten minutes
Pairs of products in the same category
The pair (A, B) is the same as the pair (B, A)

✓Directional pairs (no inequality)

Customer A referred customer B
Order O1 was returned and replaced by O2
Employee A reports to manager B
The pair (A, B) is meaningfully different from (B, A)

Triple-and-higher cardinality

The interviewer will sometimes ask 'find triples of customers in the same city' or 'find three orders placed by the same customer within one hour.' The shape extends naturally: three aliases (c1, c2, c3), the inequality chained across all three (c1.id < c2.id AND c2.id < c3.id), and the conditions applied between every pair. The chained inequality is the deduplication strategy generalized: it produces each unordered triple exactly once. Walk through the cardinality before writing: triples grow by N³ in the worst case, so the join conditions must be tight.

When you write the deduplication inequality, narrate the alternatives. 'I am using strict less-than (<), so the same pair never appears in both orders and self-pairs are dropped. If the interviewer asked for both directions, I would use != instead, which keeps both orders but still drops self-pairs.' That alternative-naming is what tells the interviewer you have made the choice deliberately, not by reflex.

Comparing a Row to Its Neighbors

Daily Life

Interviews

Decide when LAG/LEAD or ROW_NUMBER replaces a self-join and when the self-join is the only clean option.

The most common follow-up at this level is 'now do this with a window function instead.' The conversion teaches you what each tool is good at. For comparing each row to the row immediately before it in a sorted order, the window function (LAG) wins on readability and usually performance. For comparing to a row defined by a join condition rather than a row position, the self-join is the only option.

The two solutions side-by-side

Question: for each transaction, return the previous transaction's amount per account. Both queries produce identical output.

	SELECT
	account_id,
	txn_date,
	amount,
	LAG(amount) OVER(PARTITION BY account_id ORDER BY txn_date) AS prior_amount
	FROM transactions ;


	SELECT
	curr.account_id,
	curr.txn_date,
	curr.amount,
	prev.amount AS prior_amount
	FROM transactions curr
	LEFT JOIN transactions prev
	ON prev.account_id = curr.account_id AND prev.txn_date < curr.txn_date AND NOT EXISTS(SELECT 1 FROM transactions other WHERE other.account_id = curr.account_id AND other.txn_date < curr.txn_date AND other.txn_date > prev.txn_date) ;

The window function is shorter, faster on most engines, and reads as a single pipeline. The self-join version requires a NOT EXISTS subquery (or a correlated max) to pick 'the immediately previous row' from a set of all-previous rows. The self-join is doing more work both syntactically and operationally. For this shape of comparison, LAG is the right reach.

When the self-join wins after all

The window function loses when the comparison is to a row defined by a join condition rather than a sorted-row position. 'For each transaction, find the most recent matching transaction from the same merchant.' LAG cannot easily express this because the merchant filter is not a row offset. The self-join handles it cleanly: join transactions to itself on merchant_id, with a directional time inequality and a NOT EXISTS to pick the most recent prior match. Whenever the 'prior row' is defined by a predicate rather than by position, self-join is the tool.

Plan implications

On a modern optimizer, LAG over a partitioned-sorted source is a single-pass operation: the engine sorts each partition once and walks it in order. The self-join with an inequality and NOT EXISTS may plan as a nested loop with subquery evaluation per outer row. On large tables this is the difference between a query that runs in seconds and one that runs in minutes. The interviewer at this level expects you to know which tool produces which plan; saying 'I would EXPLAIN this to see whether the engine planned the self-join as a hash join or a nested loop' is the move that proves you have tuned this kind of query before.

Both tools have legitimate uses. The mistake is reaching for one without articulating why. Saying 'I default to LAG for adjacent-row comparisons and switch to self-join only when the comparison condition cannot be expressed as a row offset' is the framework. The framework is what the interviewer is reading you for.

Self-Join vs LAG for Adjacent Rows

Daily Life

Interviews

Handle recursive manager chains, multi-level comparisons, and know when to pivot to a recursive CTE.

The escalation at this level is depth. A self-join handles one level of hierarchy. Two self-joins handle two levels. Three handle three. At some depth, the chained-join approach breaks down: either because the hierarchy is unbounded, or because the code becomes unreadable, or because the engine's join planner gives up. The pivot point is when you switch to a recursive CTE. Knowing where that pivot point is , and being able to defend the choice , is the depth signal at this level.

The chained-self-join approach

	/* Three levels of hierarchy: employee, manager, grand-manager */
	SELECT
	e.name AS employee,
	m.name AS manager,
	gm.name AS grand_manager,
	ggm.name AS great_grand_manager
	FROM employees AS e
	LEFT JOIN employees AS m
	ON e.manager_id = m.employee_id
	LEFT JOIN employees AS gm
	ON m.manager_id = gm.employee_id
	LEFT JOIN employees AS ggm
	ON gm.manager_id = ggm.employee_id

Each level adds one LEFT JOIN. The query reads top-down: employee, their manager, their manager's manager, and so on. LEFT JOIN handles missing levels (the CEO has no manager). The pattern works cleanly up to about four levels; beyond that the query becomes unreadable and the join planner's combinatorial space explodes.

When to switch to a recursive CTE

If the question says 'all reports, direct and indirect' or 'walk the tree until you find the root,' the depth is unbounded. Chained joins cannot express unbounded depth. A recursive CTE walks the hierarchy one level per iteration, stopping when no new rows are added. The shape is different from a self-join: the recursive CTE references itself in the recursive step, and the engine evaluates it iteratively rather than as a single SQL pass.

	/* All employees under a target manager, any depth, with a depth column */
	WITH RECURSIVE subtree AS (
	/* Anchor: direct reports of the target manager */
	SELECT
	employee_id,
	name,
	manager_id,
	1 AS depth
	FROM employees
	WHERE manager_id = 42

	UNION ALL

	/* Recursive step: walk one level down per iteration */
	SELECT
	e.employee_id,
	e.name,
	e.manager_id,
	s.depth + 1
	FROM employees AS e
	INNER JOIN subtree AS s
	ON e.manager_id = s.employee_id
	)

	SELECT
	*
	FROM subtree
	ORDER BY depth, name

The anchor query finds the direct reports of the target manager. The recursive step joins the previous level's results back to employees to find one more level down. The depth column increments each iteration, giving you a quick way to filter or sort by depth. Recursive CTEs are supported on Postgres, SQL Server, BigQuery, Snowflake, and MySQL 8.0+. Mention the dialect constraint on older MySQL: 'this requires MySQL 8.0 or later; on 5.7 we would need a stored procedure or an application-side traversal.'

The cost model and when to switch tools

Each iteration of a recursive CTE is a join between the previous iteration's results and the source table. The engine cannot estimate the depth in advance, so the optimizer treats it as a loop with unknown bounds. For wide hierarchies (many siblings per level) and deep hierarchies (many levels), the cost grows as the product of width and depth. Adding indexes on the join column (manager_id) is essential. Mention this when the interviewer asks about scale: 'recursive CTEs need an index on the join column to keep each iteration efficient; without it the cost grows quadratically with depth.' The shape decision between chained joins and a recursive CTE follows from the question's depth, not from personal preference. Chained joins fit fixed-depth, columnar output; recursive CTEs fit unbounded depth, row-per-descendant output.

manager_id, parent_id, or whichever column the recursive step joins on. Without it, each iteration scans the source table.
real-world hierarchies have cycles; a visited-set in the recursive step prevents infinite loops.
engines have a default recursion limit (Postgres: 65535 iterations; SQL Server: 100 by default). Set explicitly for production queries.

•Chained self-joins win when

Depth is known and small (1-4 levels)
Each level needs to be a separate column in the output
The team's house style avoids recursive constructs
Performance is critical and you can hand-optimize the join order

✓Recursive CTEs win when

Depth is unbounded or unknown
The output is one row per descendant with a depth column
Aggregates need to be computed across the entire subtree
The hierarchy has natural cycles that need explicit termination

How candidates talk about self-joins, ranked by what the interviewer hears

The vocabulary you choose when narrating a self-join is the second-strongest signal after the SQL itself. Reading the query out loud as a join between two roles, naming the cardinality before writing, and reaching for the right alternative tool when the comparison shape changes are the moves the interviewer is scoring. The phrasing below maps the same situation onto two different registers.

Situation	Phrasing that flatlines	Phrasing that lands
You see a same-table comparison	"I'll join the table to itself."	"This is a self-join. I'll alias employees as e for the employee and m for the manager; the join condition expresses the relationship."
The interviewer hands you a symmetric pair question	"I'll add WHERE != to drop duplicates."	"I'll add c1.id < c2.id to the ON clause so each unordered pair appears exactly once and self-pairs are dropped."
You spot that the comparison is adjacent-row	"I'll self-join and find the previous row."	"This is an adjacent-row comparison. LAG over (PARTITION BY ... ORDER BY ...) is cleaner; the self-join is the right reach when the match is defined by a predicate, not a row position."
The interviewer mentions billion-row scale	"I'd run it and see."	"Unconstrained, this fans out to N². I'd bound the ON clause with a time window or a key equality before deploying. EXPLAIN would tell me whether the optimizer planned it as a hash join or a nested loop."
The interviewer asks about unbounded depth	"I'd write a bunch of LEFT JOINs."	"For unbounded depth I'd switch to a recursive CTE with cycle detection. Chained joins cap out around four levels."

Cycle detection: the subtle bug

Real-world hierarchies sometimes have cycles (an org chart with a temporary reporting loop, a category graph with cross-references). A recursive CTE without cycle detection runs forever (or until the engine's recursion limit kicks in). The fix is a 'visited' set that tracks employee_ids already seen in the path. Adding cycle detection is a small extra clause and a big signal of production experience. At Meta in 2021, an internal HR reporting query that walked the management chain ran fine for years until a temporary reorg created a two-week reporting loop (employee A's manager was promoted to employee A's reorg-temporary skip-level); the next nightly run of the reporting job hit Postgres's default recursion limit and failed silently, blank dashboards for two days while everyone assumed the data team was migrating something. Defaulting to a visited-set in any production recursive CTE costs nothing and prevents the failure mode.

	WITH RECURSIVE subtree AS(SELECT employee_id, name, manager_id, 1 AS depth, ARRAY employee_id AS path FROM employees WHERE manager_id = 42 UNION ALL SELECT e.employee_id, e.name, e.manager_id, s.depth + 1, s.path \|\| e.employee_id FROM employees e JOIN subtree s ON e.manager_id = s.employee_id WHERE NOT(e.employee_id = ANY(s.path)))


	SELECT *
	FROM subtree ;

The path array accumulates the employee_ids visited so far. The WHERE clause in the recursive step prevents revisiting any employee already in the path. The dialect varies (Postgres uses ARRAY, SQL Server uses XML or string concatenation, BigQuery uses an array of structs), but the idea is the same: keep a breadcrumb trail and refuse to add a node that has already been visited. Mention cycle detection unprompted when the recursive CTE comes up; many candidates omit it and the bug only surfaces in production data with cycles.

The closing summary

Close with a four-sentence wrap. 'I used a self-join with two aliases representing the two roles, and the join condition expressed the relationship between them. For symmetric pair questions I used the < inequality on a unique column to deduplicate without dropping legitimate pairs. For adjacent-row comparisons I would prefer LAG; for unbounded depth I would switch to a recursive CTE with cycle detection. The choice between self-join and the alternatives is driven by the shape of the comparison, not by which tool I remember first.' Four sentences. Each names a different decision the interviewer is scoring.

❯❯❯PUTTING IT ALL TOGETHER

> You are in a data engineering interview. The interviewer asks: 'Find pairs of orders by the same customer placed within ten minutes of each other.'

You frame the cardinality first: 'A self-join on customer alone could fan out to N times N rows. I will bound it with three conditions: same-customer equality, a directional time inequality to deduplicate, and a ten-minute upper bound to limit the cross-product.'

You write the query with all three constraints in the ON clause: o2.customer_id = o1.customer_id, o2.order_time > o1.order_time, o2.order_time <= o1.order_time + INTERVAL '10 minutes'.

You narrate the inequalities while typing: 'The first equality binds the pair. The strict greater-than gives me directional pairs and drops self-pairs. The upper bound caps the time window.'

Follow-up: 'Now find the most recent prior order per order, not all matching orders.' You say: 'That is no longer a pair-finding question; it is an adjacent-row comparison. LAG over (PARTITION BY customer_id ORDER BY order_time) is the clean reach. The self-join only wins when the comparison is to a row defined by a predicate, not by sorted position.'

Closing: 'For unbounded-depth hierarchies I would switch to a recursive CTE with cycle detection. The tool choice is driven by the shape of the comparison, not by which one I remember first.'

KEY TAKEAWAYS

An unconstrained self-join fans out to N squared rows, so name the cardinality before writing SQL and bound the ON clause with an equality, a directional inequality, and a window predicate.

Keep the inequality and the time window in the ON clause rather than WHERE so the engine prunes candidate matches during the join instead of materializing the cross product and filtering after.

Symmetric pair questions need c1.customer_id < c2.customer_id on a column that is unique per row; the strict less-than drops self-pairs and keeps each unordered pair once, and the same idea chains to c1.id < c2.id AND c2.id < c3.id for triples.

LAG wins whenever the comparison is to an adjacent row in a sorted order, because it plans as a single sorted pass; the self-join is the only clean option when the prior row is defined by a predicate such as same merchant rather than by row offset.

Chained LEFT JOIN calls handle a hierarchy up to about four levels; unbounded depth needs a RECURSIVE CTE with an index on manager_id and a visited-path array so a reporting loop cannot spin forever.

The table joins itself when the question compares rows to other rows

Category: SQL
Difficulty: intermediate
Duration: 25 minutes
Challenges: 0 hands-on challenges

Topics covered: Directional Pairs and Double-Counting, Self-Join vs Window Function, Avoiding the (a,b)/(b,a) Duplicate, Comparing a Row to Its Neighbors, Self-Join vs LAG for Adjacent Rows

Lesson Sections

Directional Pairs and Double-Counting (concepts: sqlSelfJoin)
The decision the interviewer is silently scoring Self-join is one of three tools for cross-row comparison. The others are window functions and recursive CTEs. Picking the right one for the shape of the comparison is the move. Defaulting to one without articulating why is the yellow flag. The interviewer is checking whether you treat the choice as a tradeoff or as a reflex. At Stripe in late 2022, a fraud detection query that should have been a sliding window comparison was written as an unconstr
Self-Join vs Window Function (concepts: sqlSelfJoin)
At this level, the aliasing and join syntax is the floor. The interesting decisions are in how you constrain the join: the predicate that determines which pairs survive, the inequality that deduplicates symmetric pairs, and the additional conditions that bound the time or value window. Each of those is a deliberate choice; sloppy choices ship as performance bugs or wrong row counts. The canonical self-join with all three constraints Three constraints in the ON clause. The first bounds the join t
Avoiding the (a,b)/(b,a) Duplicate (concepts: sqlSelfJoin)
Symmetric pair questions ('find customers in the same region as another customer') are where the duplicate-pair bug ships most often. The naive query returns each pair twice plus every self-pair, inflating the row count by 2N + N². The fix is one inequality, but the choice of which column to use as the tiebreaker has implications for correctness, cardinality, and deduplication semantics. The bug and the standard fix Choosing the column and the direction Two decisions sit underneath the inequalit
Comparing a Row to Its Neighbors (concepts: sqlSelfJoin)
The most common follow-up at this level is 'now do this with a window function instead.' The conversion teaches you what each tool is good at. For comparing each row to the row immediately before it in a sorted order, the window function (LAG) wins on readability and usually performance. For comparing to a row defined by a join condition rather than a row position, the self-join is the only option. The two solutions side-by-side Question: for each transaction, return the previous transaction's a
Self-Join vs LAG for Adjacent Rows (concepts: sqlRecursiveCte)
The escalation at this level is depth. A self-join handles one level of hierarchy. Two self-joins handle two levels. Three handle three. At some depth, the chained-join approach breaks down: either because the hierarchy is unbounded, or because the code becomes unreadable, or because the engine's join planner gives up. The pivot point is when you switch to a recursive CTE. Knowing where that pivot point is , and being able to defend the choice , is the depth signal at this level. The chained-sel