SQL Self Join Interview Questions

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 andmanager, or a and b.

Common self-join interview prompts

1. Find pairs of customers in the same city.
2. Find users who returned after their first session.
3. Compare each order with an earlier order from the same customer.
4. Find products in the same category with similar prices.
5. Explain how to avoid duplicate mirrored pairs.

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 likeb.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 withmax() or use lag(). Without that final step, one current row can match several 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.

Practice next

Review basic joins,window functions, and theretention cohort mission. Then compare self joins with EXISTS patterns for "has another matching row" questions.