Predict: a recursive team path

Predict each sample team’s depth below the top level.

what to return

Columns: team_iddepth in this order
Rows: in the order asked
Watch for: Use only the sample rows shown. NULL is not zero or empty text. Keep duplicate rows.
Skill: Reason about the exact result of a fixed SQL query. Refresher: Recursive CTEs →

data you can query

Developer Platform dataset: the first 6 rows of each table. Under each column name: the kind of value it holds, and which table it links to. Your queries run on all rows.

team_sample3 columns · 5 rows

team_id	team_name	parent_team_id
49	Infrastructure	NULL
50	Platform	NULL
51	API	50
52	Data	51
53	Messaging	52

team_id: 49
team_name: Infrastructure
parent_team_id: NULL

team_id: 50
team_name: Platform
parent_team_id: NULL

team_id: 51
team_name: API
parent_team_id: 50

team_id: 52
team_name: Data
parent_team_id: 51

team_id: 53
team_name: Messaging
parent_team_id: 52

all 5 rows

predict.sql

WITH RECURSIVE t(team_id, depth) AS (
  SELECT team_id, 0
  FROM team_sample
  WHERE parent_team_id IS NULL
  UNION ALL
  SELECT s.team_id, t.depth + 1
  FROM team_sample s
  JOIN t ON s.parent_team_id = t.team_id
)
SELECT team_id, depth
FROM t
ORDER BY team_id

Read the query · predict its result

WITH RECURSIVE t(team_id, depth) AS (SELECT team_id, 0 FROM team_sample WHERE parent_team_id IS NULL UNION ALL SELECT s.team_id, t.depth + 1 FROM team_sample s JOIN t ON s.parent_team_id = t.team_id) SELECT team_id, depth FROM t ORDER BY team_id

Look at the sample tables and SQL. Enter the rows you think the query returns, in this column order: team_id, depth. Put each row in square brackets, then wrap all rows in another pair of brackets (JSON format). For example, [[1,null]] is one row with 2 values. Replace these with your predicted values. Use numbers without quotes, text in double quotes, and null for SQL NULL. Keep repeated rows. Use [] for no rows. Keep rows in the order specified by ORDER BY.

Your predicted rows

Showing the result or using a hint means a correct prediction counts as solved with help. Check prediction tests your answer without showing the result.

Hints and the answer are always available. Using them counts the question as solved with help.