CodeBlanks Logo Icon CodeBlanks
Login Register

Tracking depth

Compute each row's depth level.

Depth level

In a recursive CTE the level column shows the hierarchy depth. It increments each step.

SELECT name, level FROM org ORDER BY level;

This query shows each person's level.

name, level is selected from org. The result is sorted by level in ascending order.

Depth level — details

level starts at 1 in the anchor, +1 in the recursive step. Depth makes the hierarchy easier to understand.

If you sort the result with ORDER BY level you see the top manager first and then the people below, level by level; WHERE level <= 2 returns only the first two levels.

Practice Depth level

Sort the recursive CTE results by the level column.

Starter code

query.sql
-- sort by level
SELECT name, level FROM org

Sample solution

query.sql
SELECT name, level FROM org ORDER BY level;

Expected output

name    level
Fatma   1
Ayşe    2

Questions and answers

Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.

1 What happens to level each step?

  • Stays the same
  • Increases by 1 Correct answer
  • Decreases
  • Resets to zero

Explanation: Level increments by +1 each step.

2 Add ORDER BY

Question 
SELECT name, level FROM org ____ level;
Answer 
SELECT name, level FROM org ORDER BY level;

Explanation: ORDER BY is the right answer.

3 Tracking depth: match the terms

Term Meaning
level depth
+1 increments
anchor 1
step loop

All lessons in Advanced

1 CTE

  1. What is a CTE?
  2. Using WITH
  3. Multiple CTEs
  4. CTE vs subquery
  5. CTE with JOIN
  6. Summary - CTE

2 Recursive CTE

  1. What is a recursive CTE?
  2. Anchor and recursive parts
  3. Walk a hierarchy
  4. Tracking depth
  5. Stopping recursion
  6. Summary - Recursive CTE

3 Window functions

  1. What is a window function?
  2. ROW_NUMBER
  3. RANK and DENSE_RANK
  4. LAG and LEAD
  5. PARTITION BY
  6. Summary - Window functions

4 Project: Analytics report

  1. Analytics design
  2. CTE summary
  3. Top users with ROW_NUMBER
  4. Growth with LAG
  5. Final report

5 Pivot and unpivot

  1. What is pivot?
  2. Pivot with CASE
  3. FILTER clause
  4. Unpivot
  5. Dynamic pivot
  6. Summary - Pivot

6 Materialized view

  1. What is a materialized view?
  2. CREATE
  3. REFRESH
  4. View vs materialized view
  5. Index on materialized views
  6. Summary - Materialized view

7 Stored procedure

  1. What is a procedure?
  2. CREATE PROCEDURE
  3. Parameters
  4. CALL
  5. Control flow
  6. Summary - Stored procedure

8 Function

  1. What is a function?
  2. CREATE FUNCTION
  3. RETURN
  4. Calling a function
  5. Function vs procedure
  6. Summary - Function

9 Trigger

  1. What is a trigger?
  2. BEFORE and AFTER
  3. INSERT trigger
  4. UPDATE trigger
  5. Write an audit trigger
  6. Summary - Trigger

10 Transaction isolation

  1. What is isolation?
  2. READ UNCOMMITTED
  3. READ COMMITTED
  4. REPEATABLE READ
  5. SERIALIZABLE
  6. Summary - Isolation

11 Locking

  1. What is a lock?
  2. Shared and exclusive locks
  3. Deadlock
  4. SELECT FOR UPDATE
  5. Lock timeout
  6. Summary - Locking

12 Query optimization

  1. What is EXPLAIN?
  2. Seq scan vs index scan
  3. Cost
  4. Sargable
  5. N+1 problem
  6. Summary - Optimization

13 Partitioning

  1. What is partitioning?
  2. Range partition
  3. List partition
  4. Hash partition
  5. Partition pruning
  6. Summary - Partitioning

14 Advanced final

  1. Hierarchy report
  2. Window analysis
  3. Procedure and trigger
  4. Optimization