DA0-002 Data Acquisition and Preparation Practice Question

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

• ✗

  A window function with ROW_NUMBER()

  Why it's wrong here

  ROW_NUMBER() assigns an arbitrary sequential integer by sort order; it carries no parent-child linkage, so it cannot derive depth from manager_id. Recursive CTEs traverse that self-referencing relationship to compute each employee's level. ROW_NUMBER() suits ranking or de-duplication within a partition, not hierarchy traversal.

• ✓

  A recursive CTE

  Why this is correct

  A recursive CTE references its own result set, walking manager_id links upward from each employee until the NULL root is reached, producing hierarchy levels. Self-joins need a known depth, and window functions cannot traverse variable-length parent-child chains.

• ✗

  A GROUP BY clause with aggregation

  Why it's wrong here

  GROUP BY collapses rows into aggregates and cannot traverse parent-child links, so it cannot produce a per-employee depth value. Aggregation suits counting or summing grouped records; hierarchy traversal requires recursion, which the correct option provides through repeated self-reference.

• ✗

  A self-join with a LEFT JOIN

  Why it's wrong here

  A single self-join matches each employee to their immediate manager only, yielding one level rather than the full depth to the NULL-rooted top. Self-joins suit fixed-depth comparisons; arbitrary-depth hierarchies need recursive querying, which repeatedly applies the join until the anchor row is reached.