Slide 17
Slide 17 text
Redshift
-- 1990ʹޏͬͨਓͷݱࡏڅ༩ҰཡΛऔಘ
-- ෦ॺ͝ͱͷڅ༩TOP3Λऔಘ
WITH ninety_hire_man_employees AS (
SELECT
*
FROM
employees.employees
WHERE
hire_date BETWEEN '1990-01-01'
AND '1990-12-31'
AND gender = 'M' ),
departments_and_manager AS (
SELECT
departments.*,
employees.last_name AS manager_last_name
FROM
employees.departments
LEFT JOIN employees.dept_manager ON departments.dept_no = dept_manager.dept_no
AND dept_manager.to_date = '9999-01-01'
LEFT JOIN employees.employees ON dept_manager.emp_no = employees.emp_no )
SELECT
*
FROM (
SELECT
departments_and_manager.dept_name,
departments_and_manager.manager_last_name,
RANK ( )
OVER (
PARTITION BY
departments_and_manager.dept_name
ORDER BY
salaries.salary DESC ) AS saraly_rank,
salaries.salary,
titles.title,
ninety_hire_man_employees.*
FROM
ninety_hire_man_employees
LEFT JOIN employees.salaries ON ninety_hire_man_employees.emp_no = salaries.emp_no
AND salaries.to_date = '9999-01-01'
LEFT JOIN employees.titles ON ninety_hire_man_employees.emp_no = titles.emp_no
AND titles.to_date = '9999-01-01'
LEFT JOIN employees.dept_emp ON ninety_hire_man_employees.emp_no = dept_emp.emp_no
AND dept_emp.to_date = '9999-01-01'
LEFT JOIN departments_and_manager ON dept_emp.dept_no = departments_and_manager.dept_no
WHERE
salaries.salary IS NOT NULL )
WHERE
saraly_rank <= 3;
https://gist.github.com/twingo-b/96cc16b6479db9da89a46c3df3d9f6d8