List the employees (name, base_salary) that earn an average value (with accuracy up to 30%) from the average earnings in teams of >2 employees

Question

Here's what I got so far:

SELECT surname, base_salary from emp p LEFT JOIN (select id_team, avg(base_salary) as s, count(*) as c from emp group by id_team) as o ON(p.id_team = o.id_team)
where p.base_salary between o.s*0.7 and o.s*1.3 and o.c >=2

On Oracle LIVE SQL I'm getting ORA-00905: missing keyword error.

Here's what the table looks like.

Answer 1

The problem with your is the use of the as keyword to alias the subquery. Oracle does not support that. Your query should just work of you remove it:

select ...
from emp p 
left join (
    select id_team, avg(base_salary) as s, count(*) as c from emp group by id_team
) as o on p.id_team = o.id_team)
--^-- here  
where ...

On the other hand, I think that you query could be phrased more efficiently using window functions:

select surname, base_salary
from (
    select 
        surname, 
        base_salary, 
        avg(base_salary) over(partition by id_team) avg_base_salary,
        count(*) over(partition by id_team) no_emps
    from emp 
) e
where no_emps > 2 and base_salary between avg_base_salary * 0.7 and avg_base_salary * 1.3