SELECT employee_id, last_name, salary, AVG(salary) over (PARTITION BY job_id) AS avgsal
FROM employees
inner join
    (
        SELECT department_id
        FROM employees
        WHERE
            last_name LIKE '%t%'
        GROUP BY department_id
    )
using(department_id)
WHERE salary > avgsal;