Databricks-Spark-Assoc Using Spark SQL Practice Question
A data engineer is building a Spark SQL pipeline that must return the top 3 highest-paid employees within each department from a Delta table named `employees` with columns `dept`, `name`, and `salary`. The engineer wants a single query that produces one row per qualifying employee, ranked by salary descending within each department, without collapsing rows. Which approach should be used?
⚠ Common exam trap
The trap here is assuming that a global `ORDER BY ... LIMIT` or a `GROUP BY` aggregate can satisfy a per-group top-N requirement, when only a window function preserves row granularity while ranking within partitions.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Use the `rank()` window function partitioned by `dept` and ordered by `salary DESC`, then filter on the rank column.
Window functions are the correct tool for top-N-per-group problems because they compute a value across a set of rows related to the current row while preserving all rows. Partitioning by department and ordering by salary descending yields a per-department rank, and filtering that rank to three returns exactly the desired rows in a single query.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use `GROUP BY dept` with the `MAX(salary)` aggregate and a `HAVING` clause limiting results to three rows.
Why it's wrong here
`GROUP BY dept` collapses each department to a single row, so you cannot return three employees per department this way. The `HAVING` clause filters groups, not rows within a group, and there is no way to express 'top 3 per group' with a simple aggregate. This approach produces at most one row per department.
- ✗
Use `ORDER BY salary DESC` on the full table and apply `LIMIT 3`.
Why it's wrong here
A global `ORDER BY` with `LIMIT 3` returns the three highest-paid employees across the entire company, not per department. It does not respect department boundaries, so a single high-paying department could occupy all three slots. This fails the 'within each department' requirement of the scenario.
- ✓
Use the `rank()` window function partitioned by `dept` and ordered by `salary DESC`, then filter on the rank column.
Why this is correct
A window function with `PARTITION BY dept ORDER BY salary DESC` assigns a rank within each department without collapsing rows, and filtering on the rank column keeps only the top three per department. This is the idiomatic Spark SQL pattern for top-N-per-group and is fully supported in Databricks SQL warehouses and clusters.
- ✗
Use `DISTINCT` on `dept` and `salary`, then sort the result with `SORT BY salary DESC`.
Why it's wrong here
`DISTINCT` removes duplicate combinations of department and salary, but it does not limit results to three per department and may drop employees who share a salary. `SORT BY` only orders within partitions and does not select top rows. This approach neither partitions the ranking nor enforces the top-three limit.
About these practice questions
Courseiva writes every Databricks-Spark-Assoc question from scratch — 295 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Databricks exam blueprint
This Databricks-Spark-Assoc practice question is part of Courseiva's free Databricks certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the Databricks-Spark-Assoc exam.