Notion·Software Engineer·Technical Phone Screen·Intermediate

Intermediate

Apr 2026

Summary

Interviewed for a Data Engineer role at Notion. The technical portion had a SQL focus and this particular question on self joins was the main thing I was sweating over.

Questions Asked (1)

Given a table that references itself (like an employees table with a manager_id column, or a transactions table pointing to a previous transaction), write a SQL query using a self join to combine each row with its related row. Walk through your aliasing approach, the join condition, and how you'd handle rows that have no match (like a top-level employee with no manager).

Data ModelingTechnical Trade-offs

Author's notes

I knew self joins conceptually but fumbled a bit on the aliasing when I started writing it out.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the table structure and the self-referencing column, then write a self join with distinct aliases for the two roles (e.g., employee and manager). Explain the join condition and explicitly address how to handle unmatched rows using LEFT JOIN, and optionally discuss alternative approaches like recursive CTEs for hierarchies.

Pro tip: Mention that while self joins work for one level, recursive CTEs are better for multi-level hierarchies—showing you understand the trade-off between simplicity and scalability. Also, always alias columns to avoid ambiguity and improve readability.

1. Clarify the schema and goal

Restate the table structure and the self-referencing column (e.g., manager_id references employee_id). Confirm what 'related row' means and what output is expected.

2. Choose aliases and join type

Assign distinct aliases like e (employee) and m (manager). Decide between INNER JOIN (only matched rows) and LEFT JOIN (include unmatched rows like top-level employees).

3. Write the join condition

Use the self-referencing column to link the two aliases: e.manager_id = m.employee_id. Select columns from both aliases, using COALESCE or CASE to handle NULLs for unmatched rows.

4. Handle unmatched rows and edge cases

Explain that LEFT JOIN keeps rows with no match, and you can label them (e.g., 'No Manager'). Discuss NULL handling and potential cycles or multiple levels.

5. Discuss alternatives and trade-offs

Mention recursive CTEs for multi-level hierarchies, and compare performance and readability of self joins vs. other methods.

Key Points to Mention

Use distinct table aliases to differentiate the two roles (e.g., e and m).
Join condition: e.manager_id = m.employee_id (or equivalent self-referencing column).
LEFT JOIN to include rows with no match, such as top-level employees or initial transactions.
Handle NULLs in output using COALESCE or CASE to provide meaningful labels.
Self joins are limited to one level; recursive CTEs are needed for multi-level hierarchies.
Performance considerations: indexing the self-referencing column and avoiding unnecessary columns.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.