In this post I wan't to prepare a query to fetch all the managers who has a male and a female employee reporting to them in every department.
Schema Setup:
create table dept
(dept_id number(3),
dept_name varchar2(100));
insert into dept (dept_id, dept_name) values (1, 'Dept A');
insert into dept (dept_id, dept_name) values (2, 'Dept B');
insert into dept (dept_id, dept_name) values (3, 'Dept C');
Schema Setup:
create table dept
(dept_id number(3),
dept_name varchar2(100));
insert into dept (dept_id, dept_name) values (1, 'Dept A');
insert into dept (dept_id, dept_name) values (2, 'Dept B');
insert into dept (dept_id, dept_name) values (3, 'Dept C');
create table emp
(number (3),
emp_name varchar2 (100),
mgr_id number (3),
dept_id number (3),
gender varchar2 (1));
Note: In the below data set only Manager 3 has both male and females reporting to him in all the departments.
insert into emp (emp_id, emp_name, mgr_id, dept_id, gender) values (1, 'Vadi', 1, 1, 'M');
insert into emp (emp_id, emp_name, mgr_id, dept_id, gender) values (2, 'Deepak', 1, 1, 'M');
insert into emp (emp_id, emp_name, mgr_id, dept_id, gender) values (3, 'Praveen', 1, 2, 'M');
insert into emp (emp_id, emp_name, mgr_id, dept_id, gender) values (4, 'Sharan', 1, 2, 'M');
insert into emp (emp_id, emp_name, mgr_id, dept_id, gender) values (5, 'Ananya', 1, 2, 'F');
insert into emp (emp_id, emp_name, mgr_id, dept_id, gender) values (6, 'Suresh', 2, 1, 'M');
insert into emp (emp_id, emp_name, mgr_id, dept_id, gender) values (7, 'Rithu', 2, 1, 'F');
insert into emp (emp_id, emp_name, mgr_id, dept_id, gender) values (8, 'Ravi', 2, 2, 'M');
insert into emp (emp_id, emp_name, mgr_id, dept_id, gender) values (9, 'Arpitha', 2, 2, 'F');
insert into emp (emp_id, emp_name, mgr_id, dept_id, gender) values (16, 'Anand', 2, 3, 'M');
insert into emp (emp_id, emp_name, mgr_id, dept_id, gender) values (10, 'Supra', 3, 1, 'M');
insert into emp (emp_id, emp_name, mgr_id, dept_id, gender) values (11, 'Shalini', 3, 1, 'F');
insert into emp (emp_id, emp_name, mgr_id, dept_id, gender) values (12, 'Murugan', 3, 2, 'M');
insert into emp (emp_id, emp_name, mgr_id, dept_id, gender) values (13, 'Kavitha', 3, 2, 'F');
insert into emp (emp_id, emp_name, mgr_id, dept_id, gender) values (14, 'Patric', 3, 3, 'M');
insert into emp (emp_id, emp_name, mgr_id, dept_id, gender) values (15, 'Lisa', 3, 3, 'F');
Solution:
Write a query on EMP.
Group employees by Manager, department and find number of genders reporting to managers.
Apply a filter to get only the group in which managers have 2 genders
Now, apply a rank function within each managers window by dept.
Finally, fetch only the managers who have the record with highest rank that is equal to the total number of departments.
Use a CTE to arrive at total number of departments.
WITH no_of_depts AS (select count (dept_id) total_no_of_dept from dept)
select mgr_id
from ( select m.mgr_id, dept_id, count (distinct m.gender) no_of_genders,
rank () over (partition by mgr_id order by dept_id) rank
from emp m
group by m.mgr_id, m.dept_id
having count (distinct m.gender) = 2
order by m.mgr_id, m.dept_id
)
where rank = (select total_no_of_dept from no_of_depts);
MGR_ID
----------
3
1 row selected.
Now, if I need to print the number of genders reporting to each manager across every department in the org, then I will need a rowset matrix to be used in my CTE for reference against the managers across every department.
select distinct mgr_id, d.dept_id
from emp m cross join dept d
group by mgr_id, d.dept_id
order by mgr_id, d.dept_id;
MGR_ID | DEPT_ID
---------- | ----------
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3
9 rows selected.
I need to join this CTE with the EMP table to get the number of genders reporting to them in each department. It will be 0,1 or 2.
0-- No employees reporting to them
1-- Either Male or Female reporting to them in the department
2-- Both Male and Female reporting to them in the department
WITH mgr_4_all_depts AS
(
select distinct mgr_id, d.dept_id
from emp m cross join dept d
group by mgr_id, d.dept_id
)
SELECT d.mgr_id, d.dept_id, COUNT (DISTINCT m.gender) no_of_genders
FROM mgr_4_all_depts d
LEFT JOIN emp m ON m.dept_id = d.dept_id AND m.mgr_id = d.mgr_id
GROUP BY d.mgr_id, d.dept_id
ORDER BY d.mgr_id, d.dept_id;
MGR_ID | DEPT_ID | NO_OF_GENDERS
---------- | ---------- | -------------
1 1 1
1 2 2
1 3 0
2 1 2
2 2 2
2 3 1
3 1 2
3 2 2
3 3 2
9 rows selected.
The above query only tells me that if there are 1 gender OR 2 genders OR no employees reporting to the managers in each department.
If I need to know the details of the gender as well, then I should be taking this CTE table and join it with employees.
Group them by manager, dept and gender.
Apply the LISTAGG analytical function to get comma separated list of genders within each group.
Apply a distinct function to eliminate the repeating groups.
WITH mgr_4_all_depts AS
(
select distinct mgr_id, d.dept_id
from emp m cross join dept d
group by mgr_id, d.dept_id
)
SELECT DISTINCT d.mgr_id, d.dept_id,
NVL(LISTAGG (m.gender, ',')
WITHIN GROUP (ORDER BY m.gender)
OVER (PARTITION BY d.mgr_id, d.dept_id),'--') genders_reporting
FROM mgr_4_all_depts d
LEFT JOIN
emp m ON m.dept_id = d.dept_id AND m.mgr_id = d.mgr_id
GROUP BY d.mgr_id, d.dept_id, m.gender
ORDER BY d.mgr_id, d.dept_id;
MGR_ID | DEPT_ID | GENDERS_REPORTING
------ | ------ | ------------------
1 1 M
1 2 F,M
1 3 --
2 1 F,M
2 2 F,M
2 3 M
3 1 F,M
3 2 F,M
3 3 F,M
