How can I implement the given SQL query using PySpark instead of SQL?

To translate the SQL query into PySpark you can use the DataFrame API with a window specification. First create a Window partitioned by department_id and ordered by salary descending. Then add a dense rank column, filter where rank == 2, and select the desired columns. Example:

from pyspark.sql import Window
from pyspark.sql.functions import dense_rank, col

w = Window.partitionBy('department_id').orderBy(col('salary').desc())

df_ranked = employees_df.withColumn('rnk', dense_rank().over(w))
result = df_ranked.filter(col('rnk') == 2).select('department_id', 'salary')

This produces the same result as the SQL. Complexity is O(n log n) due to the sort within each partition. The approach scales because Spark handles the partitioning and sorting in parallel.