Last answered:

20 Sept 2023

Posted on:

25 Feb 2023

2

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)
Posted on:

20 Sept 2023

0

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. 

Submit an answer