PostgreSQL CTE top earners #47

-- PostgreSQL sample schema
CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  dept_id INT REFERENCES departments(id),
  salary NUMERIC(10,2),
  hired DATE
);
CREATE TABLE departments (id SERIAL PRIMARY KEY, name TEXT);
INSERT INTO employees (name, dept_id, salary, hired) VALUES
  ('Ali', 1, 90000.00, '2020-01-15'),
  ('Sara', 2, 120000.00, '2019-06-01'),
  ('Raj', 1, 75000.00, '2021-03-20');
INSERT INTO departments (name) VALUES ('Engineering'), ('Sales');
WITH ranked AS (
  SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rk
  FROM employees
)
SELECT name, salary FROM ranked WHERE rk <= 3;