Resolved: Joining employees in the Outer Query
Why did we join employees table in the outer query?
Is it not enough just to join cte1 and cte2 like that
with cte1 as
(select
avg(s.salary) as all_time_avg_salary
from
salaries s),
cte2 as
(select
e.emp_no, max(s.salary) as salary
from
salaries s
join employees e on e.emp_no = s.emp_no and e.gender = 'f'
group by e.emp_no)
select
sum(case when c2.salary > all_time_avg_salary then 1 else 0 end) as highest_above_average
from
cte2 c2
cross join cte1 c1;
1 answers ( 1 marked as helpful)
Just joining cte1 and cte2 and leaving emplyees table in outer query yields the same result. This has been explained in detail in next lecture.