SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 110 of 250

Moderate

Find nodes reachable within two steps

Your task

Starting from node 1, return each distinct target node reached using one or two directed edges. Show node_id and sort numerically.

A hop limit bounds traversal even when the graph contains a cycle.

Sample data

Table:edges
sourcetarget
12
13
24
42
45
67

Expected output

node_id
2
3
4
Show a hint

Track depth in a recursive CTE and take DISTINCT node IDs at depths 1 and 2.

Recursive CTEs and hierarchies guide

Start with an anchor and a stopping rule

The anchor seeds the recursive CTE. The recursive term reads previously generated rows and produces new ones. When n reaches 16, the WHERE condition stops that branch. The final SELECT controls display order.

WITH RECURSIVE powers(n) AS (
  VALUES (1)
  UNION ALL
  SELECT n * 2 FROM powers WHERE n < 16
)
SELECT n FROM powers ORDER BY n;

Walk from a parent to its children

The categories table stores parent_id on each child. Anchor on a known category, then match the next child's parent_id to the current node's id. Carry depth alongside the ID whenever the result needs to say how far it traveled.

WITH RECURSIVE tree(id, name, depth) AS (
  SELECT id, name, 0 FROM categories WHERE name = 'Music'
  UNION ALL
  SELECT c.id, c.name, t.depth + 1
  FROM categories AS c JOIN tree AS t ON c.parent_id = t.id
)
SELECT name, depth FROM tree ORDER BY depth, name;

Handle cycles in a graph

A graph can lead back to a node you already visited. For reachability, keep only the node ID in the recursive result and use UNION so duplicate nodes are discarded. Adding a changing depth column would make repeat visits distinct rows and defeat this technique.

WITH RECURSIVE reachable(node) AS (
  VALUES (2)
  UNION
  SELECT e.target FROM edges AS e
  JOIN reachable AS r ON e.source = r.node
)
SELECT node FROM reachable ORDER BY node;
SQLiteCtrl / ⌘ + Enter

Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.

Results

Loading SQLite…