Find the average total compensation based on employee titles anda gender. Total compensation is calculated by adding both the salary and bonus of each employee. However, not every employee receives a bonus so disregard employees without bonuses in your calculation. Employee can receive more than one bonus.
Output the employee title, gender (i.e., sex), along with the average total compensation.
table name: sf_employee
table name: sf_bonus
Solution:
with cte as
(
select distinct e.employee_title,e.sex,(sum(b.bonus)+e.salary) as amount
from sf_employee as e
right join sf_bonus as b
on e.id = b.worker_ref_id
group by e.employee_title,e.sex,e.salary
)
select employee_title,sex,avg(amount) from cte group by employee_title,sex