twingo-b/redshift_sample.sql

## redshift_sample.sql
-- 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;
	-- 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;