Count(salary) and gender
My understanding is that we are counting the number of either male or female salaries (depending on the task), but avg(salary) is calculated regardless of gender. Am I correct? If so, maybe it would be better to include count of all salaries as well so that the calculations are consistent and comparable?
Here is my solutuon:
with cte_salary as (
avg(salary) as avg_salary,
count(salary) as count_salary
sum(case when s.salary<c.avg_salary then 1 else 0 end),
employees e on e.emp_no = s.emp_no and e.gender = 'M'
cross join cte_salary c;