Using the same dataset, write a Spark query to find the top N highest salaries per department.

Question

Assisting AI · Accepted Answer

To retrieve the top N salaries per department, use a window function with a parameter for N. For example, if N=3:

```python
from pyspark.sql import Window
from pyspark.sql.functions import row_number

N = 3
w = Window.partitionBy("department").orderBy(col("salary").desc())
result = df.withColumn("rank", row_number().over(w))\
           .filter(col("rank") <= N)\
           .select("department", "salary")
```

You can replace `row_number()` with `rank()` if you want to allow ties. In SQL:

```sql
SELECT department, salary
FROM (
  SELECT department, salary,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
) t
WHERE rn <= 3;
```

Both approaches are efficient because Spark can push the window operation into the execution plan. Complexity is O(n log n) per partition, but distributed sorting keeps it scalable.

Using the same dataset, write a Spark query to find the top N highest salaries per department.

💡 Model Answer

🎤 Get questions like this answered in real-time