Research Lab Reporting Chain Under the Director

Problem

A research institute stores its staff in `lab_members`. Each row has a `member_id`, a `member_name`, the `advisor_id` of the person they report to, and their monthly `stipend`. Exactly one person — the lab director — has a NULL `advisor_id`, and everyone else reports (directly or indirectly) up to that director.

Return every member who sits somewhere beneath the director, with these columns: `report_id` (the member's id), `report_name` (their name), `depth` — how many levels below the director they are (the director's direct reports are depth 1, their reports are depth 2, and so on), and `stipend_gap` — the member's stipend minus the director's stipend.

Order the result by `depth`, then by `report_id`.

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 5 rows with the columns report_id, report_name, depth, stipend_gap.

Starter code (SQL)

SELECT *
FROM lab_members;

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