# Task: Find distributions of titles, salaries and genders over different departments.

from dept_emp
join salaries side:left [
  (salaries.emp_no = dept_emp.emp_no) and s"(salaries.from_date, salaries.to_date) OVERLAPS (dept_emp.from_date, dept_emp.to_date)"]
aggregate by:[dept_emp.emp_no, dept_emp.dept_no] [
  salary: average salaries.salary
]
join employees [emp_no]
join titles [emp_no]
select [dept_no, salary, employees.gender, titles.title]