SQL (PostgreSQL, MySQL, SQLite) Module 7 – Common table expressions and recursive queries

Querying hierarchies: org charts and trees

Walk a parent-child table down to every report and up to the top with a recursive CTE, carry depth and a path, and stop the cycles that bad data creates.

Intermediate
35 minutes
Examples run with sql.js 1.14.2
By MySmartCoPilot

Free: A tree in one table · Walking down: everyone under a manager · Walking up: the chain to the top · 3 more sections. With a pass: 2 in-depth parts and 3 interview answers, about 22 minutes.

What you will learn

Walk a parent-child hierarchy downwards to every report and upwards to the top
Compute the depth of each node and its path from the root for sorting and indenting
Detect and stop the cycles that bad data creates in a hierarchy
Order a tree depth-first or breadth-first, with sort keys that stay correct
Count the whole team under every manager and choose the index the walk needs

Before you start

What you’ll learn · Before you start

What you will learn

Walk a parent-child hierarchy downwards to every report and upwards to the top
Compute the depth of each node and its path from the root for sorting and indenting
Detect and stop the cycles that bad data creates in a hierarchy
Order a tree depth-first or breadth-first, with sort keys that stay correct
Count the whole team under every manager and choose the index the walk needs

Before you start

On this page

Most hierarchies in SQL are stored as an adjacency list: each row holds the id of its parent, such as an employee’s manager_id or a category’s parent_id. One join finds a person’s manager or direct reports; a recursive CTE follows the links as far as they go. Walking down from a manager returns everyone below, at any depth. Walking up from a person returns every manager above, up to the top. Carry two more columns along the walk, the depth and the path from the top, and the same query indents an org chart, keeps each team under its manager and builds breadcrumbs such as Groceries > Beverages > Tea. Comment threads, folders, bills of materials and product categories are queried the same way.

A tree in one table

The examples use a company of 12 people. Every row names the person’s manager by id, except the chief executive’s, whose manager_id is NULL:

The employees table drawn as a tree

Text description of the diagram

Each box is a row of the employees table, with its id. Each line goes from a manager down to someone whose manager_id holds the manager's id.

Kavya (1), the chief executive, has no manager. Arjun (2, engineering), Fatima (3, sales) and Gurpreet (10, finance) report to her.
Rohan (4) and Sneha (5) report to Arjun. Vikram (6) and Ananya (7) report to Rohan; Imran (8) and Lakshmi (9) report to Sneha.
Neha (12) reports to Fatima, and Tenzin (11) reports to Gurpreet.

The tree has four levels: depth 0 for Kavya down to depth 3 for the four people in Rohan's and Sneha's teams.

The table stores only the links, one per person. Teams, chains of command and levels are not stored anywhere; each query computes the part it needs from the links.

Walking down: everyone under a manager

Everyone under Arjun, with their depth SQL · reports_of.sql

-- A small company of 12 people. manager_id points to the person each one reports
-- to; the chief executive has none. Salaries are monthly, in rupees.
CREATE TABLE employees (
  id         INTEGER PRIMARY KEY,
  name       TEXT    NOT NULL,
  title      TEXT    NOT NULL,
  manager_id INTEGER REFERENCES employees (id),
  salary     INTEGER NOT NULL
);
INSERT INTO employees VALUES
  (1,  'Kavya',    'Chief executive',     NULL, 400000),
  (2,  'Arjun',    'Head of engineering', 1,    300000),
  (3,  'Fatima',   'Head of sales',       1,    250000),
  (4,  'Rohan',    'Engineering manager', 2,    200000),
  (5,  'Sneha',    'Engineering manager', 2,    210000),
  (6,  'Vikram',   'Developer',           4,    150000),
  (7,  'Ananya',   'Developer',           4,    140000),
  (8,  'Imran',    'Developer',           5,    145000),
  (9,  'Lakshmi',  'Tester',              5,    120000),
  (10, 'Gurpreet', 'Head of finance',     1,    260000),
  (11, 'Tenzin',   'Accountant',          10,   110000),
  (12, 'Neha',     'Account executive',   3,    100000);

-- Everyone under Arjun (id 2), directly or not, and how many levels down they are.
WITH RECURSIVE team(id, name, title, depth) AS (
  SELECT id, name, title, 1
  FROM employees
  WHERE manager_id = 2                      -- anchor: Arjun's direct reports
  UNION ALL
  SELECT e.id, e.name, e.title, t.depth + 1
  FROM employees AS e
  JOIN team AS t ON e.manager_id = t.id     -- step: the reports of the last level
)
SELECT id, name, title, depth
FROM team
ORDER BY depth, id;

Output

┌────┬─────────┬─────────────────────┬───────┐
│ id │  name   │        title        │ depth │
├────┼─────────┼─────────────────────┼───────┤
│ 4  │ Rohan   │ Engineering manager │ 1     │
│ 5  │ Sneha   │ Engineering manager │ 1     │
│ 6  │ Vikram  │ Developer           │ 2     │
│ 7  │ Ananya  │ Developer           │ 2     │
│ 8  │ Imran   │ Developer           │ 2     │
│ 9  │ Lakshmi │ Tester              │ 2     │
└────┴─────────┴─────────────────────┴───────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < reports_of.sql

The anchor selects Arjun’s direct reports at depth 1. Each pass of the step joins employees to the rows of the last pass on e.manager_id = t.id: the people whose manager is someone found in the pass before. Rohan and Sneha come first, then their four reports at depth 2. The walk ends when a pass finds nobody, here after the developers and the tester, who manage no one. To include Arjun himself, start the anchor from WHERE id = 2 at depth 0 instead: the step stays the same.

Walking up: the chain to the top

Walking up turns the join around: the next row is the manager of the row just found, m.id = c.manager_id. The same query, run on a table of categories, builds a breadcrumb:

Lakshmi's chain of managers, and a category breadcrumb SQL · chain_up.sql

-- A small company of 12 people. manager_id points to the person each one reports
-- to; the chief executive has none. Salaries are monthly, in rupees.
CREATE TABLE employees (
  id         INTEGER PRIMARY KEY,
  name       TEXT    NOT NULL,
  title      TEXT    NOT NULL,
  manager_id INTEGER REFERENCES employees (id),
  salary     INTEGER NOT NULL
);
INSERT INTO employees VALUES
  (1,  'Kavya',    'Chief executive',     NULL, 400000),
  (2,  'Arjun',    'Head of engineering', 1,    300000),
  (3,  'Fatima',   'Head of sales',       1,    250000),
  (4,  'Rohan',    'Engineering manager', 2,    200000),
  (5,  'Sneha',    'Engineering manager', 2,    210000),
  (6,  'Vikram',   'Developer',           4,    150000),
  (7,  'Ananya',   'Developer',           4,    140000),
  (8,  'Imran',    'Developer',           5,    145000),
  (9,  'Lakshmi',  'Tester',              5,    120000),
  (10, 'Gurpreet', 'Head of finance',     1,    260000),
  (11, 'Tenzin',   'Accountant',          10,   110000),
  (12, 'Neha',     'Account executive',   3,    100000);

-- From Lakshmi (id 9) up to the chief executive: each step goes to the manager.
WITH RECURSIVE chain(id, name, title, manager_id, level) AS (
  SELECT id, name, title, manager_id, 0
  FROM employees
  WHERE id = 9
  UNION ALL
  SELECT m.id, m.name, m.title, m.manager_id, c.level + 1
  FROM employees AS m
  JOIN chain AS c ON m.id = c.manager_id
)
SELECT level, name, title
FROM chain
ORDER BY level;

-- Categories of a shop, and the breadcrumb of Green tea, built from the top down.
CREATE TABLE categories (
  id        INTEGER PRIMARY KEY,
  name      TEXT    NOT NULL,
  parent_id INTEGER REFERENCES categories (id)
);
INSERT INTO categories VALUES
  (1, 'Groceries', NULL), (2, 'Beverages', 1), (3, 'Tea', 2),
  (4, 'Green tea', 3),    (5, 'Staples', 1),   (6, 'Rice', 5);

WITH RECURSIVE up(id, parent_id, crumb) AS (
  SELECT id, parent_id, name FROM categories WHERE id = 4
  UNION ALL
  SELECT c.id, c.parent_id, c.name || ' > ' || u.crumb
  FROM categories AS c
  JOIN up AS u ON c.id = u.parent_id
)
SELECT crumb AS breadcrumb FROM up WHERE parent_id IS NULL;

Output

┌───────┬─────────┬─────────────────────┐
│ level │  name   │        title        │
├───────┼─────────┼─────────────────────┤
│ 0     │ Lakshmi │ Tester              │
│ 1     │ Sneha   │ Engineering manager │
│ 2     │ Arjun   │ Head of engineering │
│ 3     │ Kavya   │ Chief executive     │
└───────┴─────────┴─────────────────────┘
┌─────────────────────────────────────────┐
│               breadcrumb                │
├─────────────────────────────────────────┤
│ Groceries > Beverages > Tea > Green tea │
└─────────────────────────────────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < chain_up.sql

Each step adds one manager, and the walk stops at Kavya, whose manager_id is NULL, so the join finds no one. The breadcrumb works the same way: every step puts the parent’s name in front of the text so far, and the row that reached the top category holds the whole trail. Walking up follows one link per step, so it never returns more rows than the tree has levels.

Depth and a path for the whole chart

Start from the top, carry the depth and the path of names, and the whole company comes out as a chart:

The company as an indented chart SQL · indented_chart.sql

-- A small company of 12 people. manager_id points to the person each one reports
-- to; the chief executive has none. Salaries are monthly, in rupees.
CREATE TABLE employees (
  id         INTEGER PRIMARY KEY,
  name       TEXT    NOT NULL,
  title      TEXT    NOT NULL,
  manager_id INTEGER REFERENCES employees (id),
  salary     INTEGER NOT NULL
);
INSERT INTO employees VALUES
  (1,  'Kavya',    'Chief executive',     NULL, 400000),
  (2,  'Arjun',    'Head of engineering', 1,    300000),
  (3,  'Fatima',   'Head of sales',       1,    250000),
  (4,  'Rohan',    'Engineering manager', 2,    200000),
  (5,  'Sneha',    'Engineering manager', 2,    210000),
  (6,  'Vikram',   'Developer',           4,    150000),
  (7,  'Ananya',   'Developer',           4,    140000),
  (8,  'Imran',    'Developer',           5,    145000),
  (9,  'Lakshmi',  'Tester',              5,    120000),
  (10, 'Gurpreet', 'Head of finance',     1,    260000),
  (11, 'Tenzin',   'Accountant',          10,   110000),
  (12, 'Neha',     'Account executive',   3,    100000);

-- The whole company as an indented chart. Each row carries its depth and the
-- path of names from the top; sorting by that path puts every team under its manager.
WITH RECURSIVE chart(id, name, title, depth, path) AS (
  SELECT id, name, title, 0, name
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.title, c.depth + 1, c.path || '/' || e.name
  FROM employees AS e
  JOIN chart AS c ON e.manager_id = c.id
)
SELECT substr('· · · · · · ', 1, 2 * depth) || name AS person, title, depth, path
FROM chart
ORDER BY path;

Output

┌───────────────┬─────────────────────┬───────┬───────────────────────────┐
│    person     │        title        │ depth │           path            │
├───────────────┼─────────────────────┼───────┼───────────────────────────┤
│ Kavya         │ Chief executive     │ 0     │ Kavya                     │
│ · Arjun       │ Head of engineering │ 1     │ Kavya/Arjun               │
│ · · Rohan     │ Engineering manager │ 2     │ Kavya/Arjun/Rohan         │
│ · · · Ananya  │ Developer           │ 3     │ Kavya/Arjun/Rohan/Ananya  │
│ · · · Vikram  │ Developer           │ 3     │ Kavya/Arjun/Rohan/Vikram  │
│ · · Sneha     │ Engineering manager │ 2     │ Kavya/Arjun/Sneha         │
│ · · · Imran   │ Developer           │ 3     │ Kavya/Arjun/Sneha/Imran   │
│ · · · Lakshmi │ Tester              │ 3     │ Kavya/Arjun/Sneha/Lakshmi │
│ · Fatima      │ Head of sales       │ 1     │ Kavya/Fatima              │
│ · · Neha      │ Account executive   │ 2     │ Kavya/Fatima/Neha         │
│ · Gurpreet    │ Head of finance     │ 1     │ Kavya/Gurpreet            │
│ · · Tenzin    │ Accountant          │ 2     │ Kavya/Gurpreet/Tenzin     │
└───────────────┴─────────────────────┴───────┴───────────────────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < indented_chart.sql

depth sets the indent. The path holds every name from the top down to the person, so sorting by it puts each manager directly above their team, with the teams in alphabetical order. A path of ids instead of names keeps the order in which people joined, but sorting such a path needs care, as the list below says.

The path is built with ||, which joins text in SQLite and PostgreSQL. In MySQL, || means OR unless the PIPES_AS_CONCAT mode is on, so write concat(c.path, '/', e.name) there.

What can go wrong

Real hierarchies break these queries in a few known ways:

A cycle in the data, such as two people recorded as each other’s manager, makes a walk loop for ever.
The order of the walk decides whether a whole team is listed before the next manager (depth-first) or every level before the next one (breadth-first).
Sort keys made of ids put 10 before 2 when they are compared as text.
Counting every team at once, every manager with every person below them, makes far more rows than the table has.
An index on manager_id lets each pass of a downward walk find the next level without reading the whole table.
Other ways to store a tree, closure tables and materialised paths, trade extra writes for faster reads; the schema design module compares them.

Your turn

Exercise · Medium · SQL

Everyone under a manager, with their depth

The table employees has the columns id, name and manager_id, the id of the person each employee reports to (NULL for the person at the top). The table target has one row with one column, manager_id: the manager you are asked about.

Return everyone who reports to that manager, directly or through other managers, with three columns:

id and name;
depth: 1 for a direct report, 2 for a report of a direct report, and so on.

Sort by depth, then by id. A manager with no team returns no rows. The starter code finds the direct reports only.

Starter code · team.sql

-- Direct reports only, so far. Make it recursive to reach every level below.
SELECT e.id, e.name, 1 AS depth
FROM employees AS e
JOIN target AS t ON e.manager_id = t.manager_id
ORDER BY depth, e.id;

The sample tests · tests.yaml

# Sample tests: each one builds a small company, names the manager in target, runs your query on it and compares
# the rows it returns, in order.
tests:
  - name: returns a manager's team at every depth
    ordered: true
    setup: |
      CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT NOT NULL, manager_id INTEGER);
      INSERT INTO employees VALUES
        (1, 'Meera', NULL), (2, 'Kabir', 1), (3, 'Ishita', 1),
        (4, 'Dev', 2), (5, 'Ritu', 2), (6, 'Aman', 4), (7, 'Zara', 3);
      CREATE TABLE target (manager_id INTEGER NOT NULL);
      INSERT INTO target VALUES (2);
    expected:
      columns: [id, name, depth]
      rows:
        - [4, 'Dev', 1]
        - [5, 'Ritu', 1]
        - [6, 'Aman', 2]
  - name: returns no rows for a person with no team
    ordered: true
    setup: |
      CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT NOT NULL, manager_id INTEGER);
      INSERT INTO employees VALUES (1, 'Meera', NULL), (2, 'Kabir', 1), (3, 'Ishita', 1);
      CREATE TABLE target (manager_id INTEGER NOT NULL);
      INSERT INTO target VALUES (3);
    expected:
      columns: [id, name, depth]
      rows: []
  - name: follows a chain four levels down
    ordered: true
    setup: |
      CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT NOT NULL, manager_id INTEGER);
      INSERT INTO employees VALUES
        (10, 'Asha', NULL), (11, 'Bilal', 10), (12, 'Chitra', 11), (13, 'Daniel', 12), (14, 'Esha', 13),
        (15, 'Farid', 10);
      CREATE TABLE target (manager_id INTEGER NOT NULL);
      INSERT INTO target VALUES (10);
    expected:
      columns: [id, name, depth]
      rows:
        - [11, 'Bilal', 1]
        - [15, 'Farid', 1]
        - [12, 'Chitra', 2]
        - [13, 'Daniel', 3]
        - [14, 'Esha', 4]

A hint

Turn the starter query into the anchor of WITH RECURSIVE team(id, name, depth) AS (…). The step joins employees to team on employees.manager_id = team.id and adds 1 to the depth; the final SELECT reads team.

The sample tests run on this device, in your browser (sql.js): nothing is sent to mysmartcopilot.com. The first run downloads SQLite (about 0.7 MB), which is kept for the next runs. A check in your browser is feedback for you, not proof that the code is right for every input.

Check yourself

4 questions about this lesson. Every answer and why it is right is on the page, behind “Show the answer”. Your score stays in this browser.

Question 1 of 4 An org chart is stored as an adjacency list. What does each row of the table hold, besides the person?
Choose one answer.

The id of that person's manager The path of ids from the top of the company The ids of every person in that person's team The number of levels between that person and the top

Show the answer to question 1

Answer: The id of that person's manager

An adjacency list stores one link per row, from each person to their parent, in a column such as manager_id. Teams, paths and depths are not stored: a recursive CTE computes them when a query needs them.
Question 2 of 4 How many rows does the lesson's query for everyone under Arjun return?
Type a number.
Your answer

Show the answer to question 2

Answer: 6

Arjun's direct reports are Rohan and Sneha (depth 1), and their reports are Vikram, Ananya, Imran and Lakshmi (depth 2): six people. Arjun himself is not in the result, because the anchor starts from his reports.
Question 3 of 4 This CTE should walk from one person up to the top. Which join condition belongs in its step?
Read the code, then choose one answer.
```
WITH RECURSIVE chain(id, manager_id) AS (
  SELECT id, manager_id FROM employees WHERE id = 9
  UNION ALL
  SELECT m.id, m.manager_id
  FROM employees AS m JOIN chain AS c ON ______
)
SELECT id FROM chain;
```
m.manager_id = c.manager_id m.id = c.id m.manager_id = c.id m.id = c.manager_id
Show the answer to question 3

Answer: m.id = c.manager_id

Walking up, the next row is the manager of the row the step reads, so its id must equal that row's manager_id. m.manager_id = c.id is the downward walk: it finds the people who report to the row instead.
Question 4 of 4 The breadcrumb query walks up from Green tea, adding each parent's name in front. Why does its final SELECT keep only the row whose parent_id is NULL?
Choose one answer.

A recursive CTE returns its rows in reverse order That row is where the walk reached the top, so its text holds every level The other rows have a NULL breadcrumb Rows with a parent are duplicates that UNION ALL could not remove

Show the answer to question 4

Answer: That row is where the walk reached the top, so its text holds every level

Each step adds one level to the text, so the rows hold "Green tea", then "Tea > Green tea", and so on. The row with no parent is the top of the tree, the last step of the walk, and its text is the whole breadcrumb.

Cycles, walk order and sort keys, with the failures reproduced Pro: opens with a Pro, Premium or Ultimate pass

Bad data turns a tree into a loop, and the order a walk returns depends on the engine unless you choose it. This part reproduces a cycle and stops it, puts a tree in depth-first and breadth-first order, and fixes a sort key that looks right until the tenth person joins.

In this part:

A cycle in the data, reproduced and stopped
Breadth-first or depth-first, three ways
The path that sorts 10 before 2, reproduced

4 runnable examples (SQL) · about 4 minutes

Opening this part with your pass…

This part opens with any MySmartCoPilot pass.

Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45

One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.

Choose a pass I have a licence key

It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).

Headcount under every manager, and what the walk costs, measured Pro: opens with a Pro, Premium or Ultimate pass

A dashboard rarely asks about one manager: it wants every manager’s team size and cost at once. This part writes that query, measures how the shape of the tree drives its cost, and reads the plan that shows which index the walk needs.

In this part:

Every manager’s team and salary in one query
The shape of the tree decides the cost, measured
The index on manager_id, read from the plan

3 runnable examples (SQL) · about 3 minutes

Opening this part with your pass…

This part opens with any MySmartCoPilot pass.

Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45

One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.

Choose a pass I have a licence key

It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).

Interview questions

Fresher to mid · Warm-up

How do you store a tree such as an org chart in one table, and how do you list everyone under a manager at any depth?

Store it as an adjacency list: one row per person with a column such as manager_id that holds the id of their manager, NULL for the person at the top, and a foreign key back to the same table. A join gives one level; a recursive CTE gives all of them. The anchor selects the manager's direct reports, WHERE manager_id = 2, at depth 1. The step joins the table to the rows of the last pass, e.manager_id = t.id, and adds 1 to the depth, so each pass finds the next level down. The walk ends when a pass finds nobody. Carry the path of ids or names from the top to sort each team under its manager, and index manager_id, because every pass looks people up by it.
Mid to senior

After an HR import, the query that shows each employee's chain of managers never finishes for some employees, and the head count computed from the top of the company dropped by 37. What happened, how do you make both queries safe, and how do you stop it from happening again?

Model answer, Pro. Model answers open with any pass; prices are in the notice on this page. First step and follow-ups
Senior to staff

A dashboard shows every manager's whole team size and monthly salary cost for a company of 50,000 people, and it must load in under a second. Write the query, estimate what it costs, and decide whether to store anything.

Model answer, Pro. First step and follow-ups
Mid to senior

Print an org chart in which every manager appears directly above their team, with each team in the order people joined, in PostgreSQL and in SQLite. Why does sorting by a path of ids written as text fail, and what do you use instead?

Model answer, Pro. First step and follow-ups

Key takeaways

An adjacency list stores each node’s parent id; a recursive CTE walks down with child.parent_id = found.id and up with parent.id = found.parent_id.
Carry the depth and the path along the walk to indent a chart, sort each team under its manager and build breadcrumbs.
A walk up returns at most one row per level; a walk down returns the whole subtree.

References

PostgreSQL documentation: WITH Queries (hierarchies, the SEARCH and CYCLE clauses) (The PostgreSQL Global Development Group)
PostgreSQL documentation: SELECT (SEARCH and CYCLE in the WITH clause) (The PostgreSQL Global Development Group)
PostgreSQL 14 release notes (SEARCH and CYCLE clauses) (The PostgreSQL Global Development Group)
PostgreSQL documentation: Array Functions and Operators (array comparison) (The PostgreSQL Global Development Group)
SQLite: The WITH Clause (hierarchical queries, depth-first and breadth-first order) (SQLite)
SQLite: The SQLite Query Optimizer Overview (automatic indexes) (SQLite)
SQLite: Built-in Aggregate Functions (group_concat with ORDER BY) (SQLite)
SQLite: EXPLAIN QUERY PLAN (SQLite)
MySQL 8.4 Reference Manual: Logical Operators (|| and PIPES_AS_CONCAT) (Oracle Corporation)
MySQL 8.4 Reference Manual: WITH (Common Table Expressions), hierarchical data traversal (Oracle Corporation)

Report a problem with this lesson

In this module: Common table expressions and recursive queries

WITH and common table expressions (CTEs) Free lesson, Pro answers: 25 minutes
Recursive CTEs, series and calendar tables Free core, Pro depth: 10 of 35 minutes free
Querying hierarchies: org charts and trees Free core, Pro depth: 13 of 35 minutes free
Graph problems in SQL: routes and reachability Free core, Pro depth: 13 of 35 minutes free

Your country

Querying hierarchies: org charts and trees

What you will learn

Before you start

What you will learn

Before you start

A tree in one table

Walking down: everyone under a manager

Walking up: the chain to the top

Depth and a path for the whole chart

What can go wrong

Your turn

Everyone under a manager, with their depth

Results of the sample tests

Check yourself

Cycles, walk order and sort keys, with the failures reproduced Pro: opens with a Pro, Premium or Ultimate pass

Headcount under every manager, and what the walk costs, measured Pro: opens with a Pro, Premium or Ultimate pass

Interview questions

How do you store a tree such as an org chart in one table, and how do you list everyone under a manager at any depth?

After an HR import, the query that shows each employee's chain of managers never finishes for some employees, and the head count computed from the top of the company dropped by 37. What happened, how do you make both queries safe, and how do you stop it from happening again?

A dashboard shows every manager's whole team size and monthly salary cost for a company of 50,000 people, and it must load in under a second. Write the query, estimate what it costs, and decide whether to store anything.

Print an org chart in which every manager appears directly above their team, with each team in the order people joined, in PostgreSQL and in SQLite. Why does sorting by a path of ids written as text fail, and what do you use instead?

Key takeaways

References

Related tools