referred_by points to the user who referred this user (NULL for organic sign-ups, called root users). The downline of a root is everyone reachable through any number of referral levels. For every root user return user_id, name, downline_size (number of users in the downline, excluding the root) and max_depth (deepest level below the root: direct referrals are depth 1; 0 when the downline is empty). Order by downline_size DESC, then user_id.
| Column | Type |
|---|---|
| user_id | INTEGER |
| name | TEXT |
| referred_by | INTEGER |
| user_id | name | referred_by |
|---|---|---|
| 1 | Aman | NULL |
| 2 | Bina | 1 |
| 3 | Chetan | 1 |
| 4 | Deepa | 2 |
| 5 | Eshan | NULL |
| 6 | Fatima | 5 |
| 7 | Gopal | NULL |
| user_id | name | downline_size | max_depth |
|---|---|---|---|
| 1 | Aman | 3 | 2 |
| 5 | Eshan | 1 | 1 |
| 7 | Gopal | 0 | 0 |
Solve it in the live editor on this page — run on sample data, then submit against hidden tests.
‹ Users Active in 3+ Consecutive Months · Customers With Rising Monthly Spend ›
{{ totalCount }} questions in the style asked at Amazon, Microsoft, Google, Meta, Flipkart and more — {{ hardPct }}% Hard. Write SQL in a real database engine in your browser, run it on sample data, then evaluate against hidden tests.
Company tags show where this type of question is commonly reported in interviews. Questions, data and solutions are written by TechTown.
{{ cur.statement }}
No submissions yet. Press Evaluate to submit your query.
{{ solutionSql }}
{{ out.error }}
You have solved your 3 free questions — nice work! Log in (free) to unlock all 150 questions, keep your streak and appear on the leaderboard.