Folder Tree Depth, Descendants and Rolled-Up Size

Problem

Table: `folders`

```text
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| folder_id | int |
| folder_name | varchar |
| parent_id | int |
| own_bytes | int |
| owner | varchar |
+-------------+---------+
folder_id is the primary key for this table.
parent_id references the folder_id of the containing folder; the single root
folder has parent_id = NULL. own_bytes is the size of files stored directly in
that folder (not counting subfolders).
```

For every folder, report:

- `depth`: the root folder is at depth 1, its direct children at depth 2, and so on.
- `descendant_count`: how many folders sit strictly below it (its entire subtree, excluding itself).
- `rolled_bytes`: the total `own_bytes` of the folder plus every folder in its subtree.

Return `folder_id`, `folder_name`, `depth`, `descendant_count`, and `rolled_bytes`, ordered by `depth` ascending, then `rolled_bytes` descending, then `folder_name` ascending.

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 10 rows with the columns folder_id, folder_name, depth, descendant_count, rolled_bytes.

Starter code (SQL)

SELECT *
FROM folders;

Related SQL questions

• 3-Consecutive-Week Marketing StreakHard
• 30-Day Rolling Average Trading VolumeHard
• 7-Day Active User Rolling MetricHard
• 7-Day Revenue Moving AverageHard
• Always Mid-Pack BakersHard
• Annual Energy Output Growth by Panel ModelHard