Question

sql challenge
your table: maintable_rzw2z
mysql version: 8.0.23
in this mysql challenge, your query should return the names of the people who are reported to (excluding null values), the number of members that report to them, and the average age of those members as an integer. the rows should be ordered by the names in alphabetical order. your output should look like the following table.
reportsto | members | average age
bob boss | 2 | 24
daniel smith | 1 | 22
david s | 1 | 25
jenny richards | 2 | 31
browse resources
powered by google
search for any documentation you might need for this problem. for example: array indexing, react props, etc.

Explanation:

Step1: Select non - null ReportsTo values

We use the WHERE clause to filter out null values in the ReportsTo column.

SELECT ReportsTo
FROM maintable_RZW2Z
WHERE ReportsTo IS NOT NULL;

Step2: Count the number of members reporting to each person

We use the COUNT function and GROUP BY to count the number of members reporting to each person in the ReportsTo column.

SELECT ReportsTo, COUNT(*) AS Members
FROM maintable_RZW2Z
WHERE ReportsTo IS NOT NULL
GROUP BY ReportsTo;

Step3: Calculate the average age of members reporting to each person

We use the AVG function to calculate the average age of members reporting to each person in the ReportsTo column. We assume there is an Age column in the table.

SELECT ReportsTo, COUNT(*) AS Members, FLOOR(AVG(Age)) AS Average_Age
FROM maintable_RZW2Z
WHERE ReportsTo IS NOT NULL
GROUP BY ReportsTo;

Step4: Order the results alphabetically

We use the ORDER BY clause to order the results by the ReportsTo column in ascending (alphabetical) order.

SELECT ReportsTo, COUNT(*) AS Members, FLOOR(AVG(Age)) AS Average_Age
FROM maintable_RZW2Z
WHERE ReportsTo IS NOT NULL
GROUP BY ReportsTo
ORDER BY ReportsTo;

Answer:

SELECT ReportsTo, COUNT(*) AS Members, FLOOR(AVG(Age)) AS Average_Age
FROM maintable_RZW2Z
WHERE ReportsTo IS NOT NULL
GROUP BY ReportsTo
ORDER BY ReportsTo;