Return every category's name, depth level, and number of products assigned directly to that category

The shape

Two layers. The recursive CTE produces one row per category with its depth attached. The main query then LEFT JOINs that hierarchy to products on category_id and counts matches per category. The LEFT JOIN is what keeps categories with zero products in the result instead of dropping them.

Clause by clause

• The recursive CTE walks the category tree:

WITH RECURSIVE cat_tree AS (
    SELECT id, name, parent_id, 1 AS depth
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM categories c
    JOIN cat_tree ct ON c.parent_id = ct.id
)

The anchor seeds the four root categories at depth 1. The recursive member matches each subcategory's parent_id to a row already in cat_tree and stamps it with ct.depth + 1. After two passes, cat_tree contains every category with the correct depth.

• The main query attaches per-category product counts:

SELECT ct.name, ct.depth, COUNT(p.id) AS product_count
FROM cat_tree ct
LEFT JOIN products p ON p.category_id = ct.id
GROUP BY ct.name, ct.depth

The LEFT JOIN keeps every category row even when no product matches; unmatched rows carry a NULL in p.id. GROUP BY ct.name, ct.depth collapses the joined rows per category, and COUNT(p.id) counts non-NULL product ids — which is zero for categories with no products. Clothing and Home & Garden both show product_count = 0; Electronics shows 12; Phones, Furniture, and Outdoor show 6, 8, and 8.

Why LEFT JOIN and COUNT(p.id), not COUNT(*)

The pairing of LEFT JOIN and COUNT(p.id) is the standard preservation pattern. LEFT JOIN keeps unmatched category rows; COUNT(p.id) correctly returns 0 for those rows because it counts non-NULL product ids. COUNT(*) would return 1 for unmatched rows, counting the placeholder NULL-padded row as if it were a product. The category rows would look like every empty category has exactly one product, which is wrong.

The trap

The LEFT JOIN direction matters. cat_tree LEFT JOIN products keeps every category and lets product matches attach; reversing it to products LEFT JOIN cat_tree would keep every product and lose the empty categories. The category side has to be the preserved side, since the question is "how many products does each category have," not "how many categories does each product belong to."