3.Top 3 salaries from each department| Important Interview Question
To retrieve the top 3 salaries from each department using PySpark, you can use the Window function along with the row_number() function. Here is a theoretical explanation:
Assuming you have a PySpark DataFrame named df with columns employee_id, department_id, and salary, you can perform the following steps:
from pyspark.sql import SparkSession
from pyspark.sql import functions as F
from pyspark.sql.window import Window
Assuming you have a SparkSession named "spark" and a DataFrame named "df"
Replace "df" with the actual DataFrame name in your code.
Define a window specification partitioned by department_id and ordered by salary in descending order
window_spec = Window.partitionBy("department_id").orderBy(F.col("salary").desc())
Add a row_number column to the DataFrame based on the defined window specification
ranked_df = df.withColumn("salary_rank", F.row_number().over(windowspec))
Filter the DataFrame to include only the top 3 salaries for each department
top_salaries_df = ranked_df.filter(F.col("salary_rank") lessthan are equal to= 3)
Show the result
top_salaries_df.show()
Explanation:
Window Specification:
We define a window specification using the Window function, specifying the partitioning column (department_id) and ordering by the salary in descending order.
Row Number Calculation:
We add a new column, salary_rank, to the DataFrame using the row_number() window function over the defined window specification. This assigns a rank to each row within its partition.
Filtering Top 3 Salaries:
We filter the DataFrame to include only the rows where the salary_rank is less than or equal to 3, effectively giving us the top 3 salaries for each department.
This PySpark code leverages window functions to achieve the desired result efficiently. Make sure to replace "df" with the actual name of your DataFrame and adjust column names based on your DataFrame schema.