PostgreSQL ROW_NUMBER partition #5 — PostgreSQL

Problem

PostgreSQL: PostgreSQL ROW_NUMBER partition #5

Hints

Use WITH for CTEs, OVER() for windows

Your practice code

Ready — edit the code above and click Run.

Solution

-- 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');
SELECT name, salary,
       ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
FROM employees;

Try solving on your own first, then reveal the official answer.

Explanation

PostgreSQL: CTEs, window functions, ILIKE, JSONB @>, EXPLAIN ANALYZE.