The Number of Employees Which Report to Each Employee | Leetcode 1731 | Crack SQL Interviews

[@learnwithchirag]

Hey there! 👋 For more interesting content, tutorials, and updates, Feel free to connect with me on
Instagram Handles :-
@createwithchirag - instagram.com/createwithchirag/
@learn.with.chirag - instagram.com/learn.with.chirag/
LinkedIn: www.linkedin.com/in/chirag-sehgal-9200111b8/
Let's stay connected and keep the creativity flowing! 💡

[@apoorvpradhan5125]

Thank you bhaiya.

[@mohammedmaqdoom4410]

thanks chirag this side

[@learnwithchirag]

Keep Learning 🎊💯

[@BlendTubeMix]

# Write your MySQL query statement below
SELECT E1.employee_id,E1.name,COUNT(E2.reports_to) AS reports_count,ROUND(AVG(E2.age)) AS average_age
FROM Employees E1, Employees E2
WHERE E1.employee_id = E2.reports_to
GROUP BY E1.employee_id, E1.name
ORDER BY E1.employee_id

[@JashwanthRavi-dy7gy]

Very thanks❤

[@prathamsharma4416]

nice one bro

[@learnwithchirag]

thanks a lot ! glad it was helpful ❤️

[@divinepandit1815]

Bhaiya ji kaisa pata hoga ki kon sa attribute kon se table se lena hai

[@learnwithchirag]

It is usually based on our joining condition. You can also reverse it ( i.e take E1 as E2 and E2 as E1 ) it will still be the same.
Try to dry run the code to get more insight. Write SELECT * and run the query once.

[@prathamsharma4416]

@@learnwithchirag yeah , that helped me, its like 2 table joined with all attributes

[@riteshchaudhary8755]

Hello brother,i want to ask one thing that why e2.age and e2.employee_id are used and when i use e1.employee_id or e1.age it is showing wrong answer.Can you explain why?

[@vineetkarmakar2305]

when you use e1.employee_id and e1.age it takes the values from the first table
and when you use e2.employee_id and e2.age it takes the value from the second table jo humne join kia hai first table ke uppar
e1.age use karega to manager ki age ka average ajaega par humne unka age ka avg chahiye jo manager ko report kar rahe hai
and humne count bhi un bando ko karna hai ki kitne bande manager ko report kar rahein hain not ki kitne manager hai jisko log report kar rahein hai