Accessing computed column in a nested query
You just have your nesting a little wrong (and introduced an unnecessary EXISTS clause):

SELECT * FROM
(
   SELECT node.category_id, ...
) AS nested 
WHERE depth = 2;


You could also try (I think):

SELECT node.category_id, (COUNT(parent.name) - 1) AS depth
...
    group by 
      node.category_id, node.lft 
HAVING (COUNT(parent.name)-1) = 2;


But please be mindful of:


Schema prefix
Old-style JOINs