LESSON 24 / 24·2 min readIntermediate

How can I practice joins and window functions?

Understand the concept

A useful SQL exercise starts with a small dataset whose expected answer can be checked manually. Window functions then demonstrate ranking while keeping each employee's fields available. The example below returns all employees tied for the highest salary in each department.

Example and explanation:

-- Small PostgreSQL practice dataset.
CREATE TEMP TABLE employees (
  id integer PRIMARY KEY,
  name text NOT NULL,
  department text NOT NULL,
  salary numeric NOT NULL
);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 80000),
  (2, 'Ravi', 'Engineering', 70000),
  (3, 'Neha', 'HR', 65000);

-- Question: who earns the highest salary in each department?
WITH ranked AS (
  SELECT *, DENSE_RANK() OVER (
    PARTITION BY department ORDER BY salary DESC
  ) AS salary_rank
  FROM employees
)
SELECT name, department, salary FROM ranked WHERE salary_rank = 1;

Answer: Asha for Engineering and Neha for HR.
DENSE_RANK also includes tied top salaries.
Add tied salaries and employees with no related child records when testing ranking and outer-join behavior.

Additional join exercise — after the temporary employees table above:

CREATE TEMP TABLE leave_requests (
  id integer PRIMARY KEY,
  employee_id integer REFERENCES employees(id),
  status text NOT NULL
);
INSERT INTO leave_requests VALUES (1, 1, 'approved'), (2, 2, 'pending');

SELECT e.name, COUNT(l.id) AS approved_count
FROM employees e
LEFT JOIN leave_requests l
  ON l.employee_id = e.id AND l.status = 'approved'
GROUP BY e.id, e.name
ORDER BY e.id;

Expected counts: Asha 1, Ravi 0, Neha 0. COUNT(l.id) ignores the unmatched NULL; COUNT(*) would count the preserved employee row.