I think this solution is also valid and it only requires one subquery select id, case when p_id is null then 'root' when id not in (select p_id from table) then 'leaf' else 'inner' end as node_type from table
Wasn't the question to pull first names of all direct reports who are contractors for a given employee. I seem to be missing the point where the interviewee mentions the manager id in the query. If there were more contractors who report to someone else, they would show up too correct ?
Don't leave your SQL interviews to chance. Sign up for Exponent's SQL interview course today: bit.ly/48LVNXz
Chapters (Powered by ChapterMe) -
00:00 - Intro
00:23 - Answer
04:20 - Real World Scenario
09:16 - Follow-up Questions
09:57 - Interview Analysis
12:33 - Outro
I think this solution is also valid and it only requires one subquery
select
id,
case
when p_id is null then 'root'
when id not in (select p_id from table) then 'leaf'
else 'inner' end as node_type
from table
Wasn't the question to pull first names of all direct reports who are contractors for a given employee. I seem to be missing the point where the interviewee mentions the manager id in the query. If there were more contractors who report to someone else, they would show up too correct ?