Interview practice

SQL Self Join Interview Questions

Practice comparing rows in the same table, pairs, gaps, and manager-style relationships.

A self join uses the same table twice with different aliases. It is useful when one row needs to be compared with another row from the same table: pairs of customers in the same city, sessions before another session, or an employee matched to a manager.

The aliases matter. Treat them like two roles in the same story: current and previous, employee and manager, or a and b.

Common self-join interview prompts

Answer each one yourself before you look: write your query or your reasoning in the box, or open the playground and run it. Then reveal the model answer and compare. Nothing you type is saved or sent anywhere.

Find pairs of customers in the same city.
Your answer Try it in the playground →
Reveal a model answer
```
select a.city,
       a.customer_id as customer_a,
       b.customer_id as customer_b
from customers as a
join customers as b
  on b.city = a.city
 and b.customer_id > a.customer_id
order by a.city, customer_a, customer_b
```
Two aliases of one table, matched on city. The inequality removes both a customer paired with themselves and the mirrored (b, a) copy of every pair.
Find users who returned after their first session.
Your answer Try it in the playground →
Reveal a model answer
```
select distinct later.user_id
from sessions as first_visit
join sessions as later
  on later.user_id = first_visit.user_id
 and later.session_date > first_visit.session_date
where first_visit.user_id is not null
order by later.user_id
```
Match each user's sessions to their later sessions; any match means they came back. DISTINCT collapses users with several returns. Anonymous sessions have no user_id and cannot be linked, so say you excluded them.
Compare each order with an earlier order from the same customer.
Your answer Try it in the playground →
Reveal a model answer
```
select cur.customer_id,
       cur.order_id,
       cur.order_date,
       max(prev.order_date) as previous_order_date
from orders as cur
join orders as prev
  on prev.customer_id = cur.customer_id
 and prev.order_date < cur.order_date
group by cur.customer_id, cur.order_id, cur.order_date
order by cur.customer_id, cur.order_date
```
Each order matches every earlier order, so aggregate to the most recent one with max(). A first order has nothing earlier and drops out of this inner join; use a LEFT JOIN to keep it. lag() over (partition by customer_id order by order_date) is the shorter modern answer.

Find products in the same category with similar prices.

Your answer Try it in the playground →

Reveal a model answer

select a.category_id,
       a.product_name as product_a,
       b.product_name as product_b,
       a.price as price_a,
       b.price as price_b
from products as a
join products as b
  on b.category_id = a.category_id
 and b.product_id > a.product_id
 and abs(a.price - b.price) <= 2
order by a.category_id, product_a, product_b

The join condition carries the business rule: same category, and a price gap within a threshold you should ask for (here $2). Keep the id inequality so each pair appears once.

Explain how to avoid duplicate mirrored pairs.
Your answer

Reveal a model answer

Joining on the shared attribute alone returns (A, B) and (B, A), plus every row paired with itself. Require one id to be strictly less than the other: that keeps exactly one ordering of each pair and removes the self-matches in one condition.

Pair rows without mirrored duplicates

select a.customer_id as customer_a,
       b.customer_id as customer_b,
       a.city
from customers as a
join customers as b
  on b.city = a.city
 and b.customer_id > a.customer_id
order by a.city, customer_a, customer_b

What matters

Pair queries need an anti-mirror condition. If customer 1 pairs with customer 2, the reverse pair is usually redundant. A condition like b.customer_id > a.customer_id keeps only one direction.

Sequence comparisons need a time condition, such as "previous date is before current date." If you need one previous row, aggregate the matched rows with max() or use lag(). Without that final step, one current row can match several earlier rows.

Compare with earlier rows

select current.session_id,
       current.user_id,
       current.session_date,
       max(previous.session_date) as previous_session_date
from sessions as current
left join sessions as previous
  on previous.user_id = current.user_id
 and previous.session_date < current.session_date
group by current.session_id, current.user_id, current.session_date

How to talk it through

Say: "I need two roles from the same table. I will alias the current row and the comparison row separately, match them on the shared entity, and add a condition that keeps only the relationship I mean."

If the query is about "previous" or "next", mention that a window function like lag() may be simpler. Self joins are still worth knowing because they work for pair and range comparisons that are not just adjacent rows.