Last answered:

20 Sept 2023

Posted on:

25 Feb 2023


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 
		avg(s.salary) as all_time_avg_salary 
		salaries s),
cte2 as 
		e.emp_no, max(s.salary) as salary 
		salaries s 
			join employees e on e.emp_no = s.emp_no and e.gender = 'f' 
	group by e.emp_no)
	sum(case when c2.salary > all_time_avg_salary then 1 else 0 end) as highest_above_average
	cte2 c2 
		cross join cte1 c1;
1 answers ( 1 marked as helpful)
Posted on:

20 Sept 2023


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