Skip to content
Onsite
13 / 44

Top 3 distinct salaries per department (top N per group)

MediumP1Coding

Tables:

department

ColumnType
idint (primary key)
nametext

employee

ColumnType
idint (primary key)
nametext
salaryint
department_idint (references department.id)

Write a query that returns the employees who earn one of the top three distinct salaries in their department. If several employees share a salary, all of them are returned. Return the columns department (the department name), employee and salary.

Schema

CREATE TABLE department (
  id int PRIMARY KEY,
  name text NOT NULL
);
CREATE TABLE employee (
  id int PRIMARY KEY,
  name text NOT NULL,
  salary int NOT NULL,
  department_id int NOT NULL REFERENCES department(id)
);

Example 1

Input
department: (1, 'IT') employee: (1, 'A', 100, 1), (2, 'B', 100, 1), (3, 'C', 90, 1), (4, 'D', 80, 1), (5, 'E', 70, 1)
Output
IT, A, 100 IT, B, 100 IT, C, 90 IT, D, 80
Why
The distinct salaries are 100, 90, 80, 70. The top three are 100, 90 and 80, so E is out.

Constraints

  • PostgreSQL dialect.
  • "Top three" counts distinct salary values, not people.
  • Return rows in any order.

Hints

PostgreSQL
Run checks the 2 visible tests, Submit all 5.