Why a Subquery Won't Capture Two Roles

Concepts covered: sqlRecursiveCte

The interviewer escalates: 'now find every employee under a given manager, recursively, including indirect reports.' A plain self-join can only walk one level. Two levels takes two joins. Three levels takes three joins. Recursive depth takes a recursive CTE. The question is testing whether you know the boundary between a self-join and a recursive query, and when to cross it. The two-level self-join If the question stops at two levels (manager and the manager's manager), chain two self-joins. The aliases multiply, but the query is still flat. Two LEFT JOINs because employees with no manager (or whose manager has no manager) should still appear with NULLs. Three aliases, one per level. This pattern stops working when the hierarchy is unbounded. Five-deep, ten-deep, or 'arbitrarily deep' org

About This Interactive Section

This section is part of the Self-Join: Beginner lesson on DataDriven, a free data engineering interview prep platform. Each section includes explanations, worked examples, and hands-on code challenges that execute in real time. SQL queries run against a live database. Python runs in a sandboxed Docker container. Data modeling problems validate against interactive schema canvases. All content is framed around what data engineering interviewers actually test at companies like Meta, Google, Amazon, Netflix, Stripe, and Databricks.

How DataDriven Lessons Work

DataDriven combines four interview rounds (SQL, Python, Data Modeling, Pipeline Architecture) with adaptive difficulty and spaced repetition. Easy problems get harder as you improve. Weak concepts resurface until you master them. Your readiness score tracks progress across every topic interviewers test. Every lesson section ends with problems you solve by writing and running real code, not by picking multiple-choice answers.