---
# System prepended metadata

title: PostgreSQL Test

---

# PostgreSQL Test

Given the following tables:

```sql
CREATE TABLE departments (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INTEGER NOT NULL REFERENCES departments (id),
  terminated_at DATE,
  salary INTEGER NOT NULL
);
```

Write a PostgreSQL query that returns, for each department, the current employees with the highest 3 unique salaries.

Current employees are employees who have not been terminated (`terminated_at` is `NULL`.)

The result should have the following columns:

| Column name | Description |
| --- | --- |
| `department` | The name of the department |
| `employee` | The name of the employee |
| `salary` | The employee’s salary |

The result can be in any order.

## Criteria

- Correctness: Your query must return the correct result, without syntax errors, when executed on a PostgreSQL 12 or above server.
- Readability: Your code should be clean, easy to understand and well-formatted. Comments are expected for any non-obvious code and engineering decisions.
- Efficiency: The query should be efficient without unnecessary joins and aggregate functions.

## Submission

Please provide a Zip archive containing a single `.sql` file with your query.

## Example

`departments`:

| id | name |
| --- | --- |
| 1 | Design |
| 2 | Sales |

`employees`:

| id | name | department_id | terminated_at | salary |
| --- | --- | --- | --- | --- |
| 1 | Jessica | 1 | NULL | 45000 |
| 2 | Hillary | 2 | 2014-03-31 | 40000 |
| 3 | Sam | 2 | NULL | 20000 |
| 4 | Matt | 1 | NULL | 50000 |
| 5 | James | 1 | NULL | 29000 |
| 6 | Charlotte | 1 | NULL | 45000 |
| 7 | Isabella | 2 | NULL | 40000 |
| 8 | Emma | 1 | NULL | 30000 |

### Output

| department | employee | salary |
| --- | --- | --- |
| Design | Matt | 50000 |
| Design | Jessica | 45000 |
| Design | Charlotte | 45000 |
| Design | Emma | 30000 |
| Sales | Isabella | 40000 |
| Sales | Sam | 20000 |

---
Copyright (c) 2022 Imperium Empires. All rights reserved.