Mentees per Mentor
Problem
Table: `member`
| Column Name | Type |
|---|---|
| member_id | int |
| full_name | varchar |
| mentor_id | int |
| years_experience | int |
`member_id` is the primary key. Each row is a member of a coding club; `mentor_id` is the `member_id` of the person who mentors them (NULL if they have no mentor).
A **mentor** is any member who has at least one other member assigned to them. For each mentor, report their `member_id`, their `full_name`, the number of mentees they have (alias `mentee_count`), and the average years of experience of those mentees rounded to the nearest integer (alias `avg_mentee_experience`). Return the result ordered by `member_id`.
Tables
Example rows — the live problem includes the full dataset.
| member_id | full_name | mentor_id | years_experience |
|---|---|---|---|
| 1 | Maya | 12 | |
| 2 | Liam | 1 | 4 |
| 3 | Noa | 1 | 6 |
Expected output
Your answer should return 2 rows with the columns member_id, full_name, mentee_count, avg_mentee_experience.
Starter code (SQL)
SELECT *
FROM member;Solve this SQL question free
Write SQL and run it instantly in your browser — even on your phone. No signup needed to try.
Solution & explanation
Create a free account to unlock the optimal solution, a step-by-step explanation, and the hidden test cases that grade your answer.
Sign up free to unlock