How can I practice joins and window functions?
Practice this questionWANT TO UNDERSTAND THE CONCEPT FIRST?Learn How can I practice joins and window functions?
Definition:
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.
Interview point:
- Explain why DENSE_RANK fits tied winners, and use a separate join example when the problem includes another table.
Adapted from 05_SQL_POSTGRESQL.md · “How can I practice joins and window functions?”. Code examples are study fragments. Difficulty and core labels are editorial estimates; version-specific behavior should be checked against your project.