Median Salary per Department (PySpark)
PYSPARK coding challenge · Difficulty: hard · Topic: Window Functions · +150 XP
Problem
Topic: Window Functions · row_number() / count() over a window · groupBy / avg
The HR compensation team wants the median salary of each department: unlike the average, it is not pulled up by one very high salary.
Using the PySpark DataFrame API (or Spark SQL), compute it.
- The median of a department is the middle salary when its salaries are sorted from lowest to highest.
- If the department has an odd number of employees, the median is the single middle salary.
- If it has an even number of employees, the median is the average of the two middle salaries (it can end in
.5). - Every employee counts, including employees who earn exactly the same salary.
- Return one row per department with its
department_idandmedian_salary(a double).
Assign the result to df_result with the columns department_id and median_salary, sorted by department_id ascending, and finish with df_result.show().
Schema
DataFrame: employees
| Column | Type | Meaning | | --- | --- | --- | | employee_id | integer | Unique id of the employee | | department_id | integer | The employee's department | | salary | integer | Annual salary |
Example 1
Input: employees
| employee_id | department_id | salary | | --- | --- | --- | | 101 | 10 | 40000 | | 102 | 10 | 60000 | | 103 | 10 | 50000 | | 201 | 20 | 70000 | | 202 | 20 | 30000 | | 203 | 20 | 130000 | | 204 | 20 | 50000 | | 301 | 30 | 45000 | | 401 | 40 | 20000 | | 402 | 40 | 95000 | | 403 | 40 | 20000 | | 404 | 40 | 30000 | | 405 | 40 | 20000 |
Output:
`
+-------------+-------------+
|department_id|median_salary|
+-------------+-------------+
| 10| 50000.0| | 20| 60000.0| | 30| 45000.0| | 40| 20000.0|
+-------------+-------------+
`
Explanation:
- Department 10: salaries 40000, 50000, 60000 (3 employees, odd): the middle one is 50000.0.
- Department 20: salaries 30000, 50000, 70000, 130000 (4 employees, even): (50000 + 70000) / 2 = 60000.0. The mean would be 70000, and taking only one middle value would give 50000.
- Department 30: a single employee: 45000.0.
- Department 40: salaries 20000, 20000, 20000, 30000, 95000 (5 employees): the middle (3rd) salary is 20000.0. Equal salaries are separate employees and each takes its own position.
Constraints
- Every department has at least one employee;
salaryis never null. - The rows are in no particular order.
employees,spark,F(pyspark.sql.functions) andWindoware ready. Do not recreateemployees.F.percentile_approxis an approximation: for an even count it returns one of the middle salaries, not their average, so it does not give the median here.- Your code is also run on hidden datasets with other departments, so it must compute the answer from
employees. The row order is graded.
What this PYSPARK challenge teaches you
“Median Salary per Department (PySpark)” is a hard-level PYSPARK challenge focused on Window Functions. Working through it gives you hands-on practice with Window functions, row_number, Median, percentile, groupBy, HR analytics — the kind of transformation you are asked to write in real data engineering work and in technical interviews. You can solve it directly in the browser: the dataset is pre-loaded, so you write the query or DataFrame code, run it, and compare your output against the expected result immediately.
Concepts covered
- Window functions
- row_number
- Median
- percentile
- groupBy
- HR analytics
How to approach it
If you get stuck, work through these steps in order before looking at a full solution — each one narrows the problem down:
- Number the salaries inside each department: F.row_number().over(Window.partitionBy('department_id').orderBy('salary')).
- Count each department's employees with F.count('*').over(Window.partitionBy('department_id')).
- The middle positions are floor((n + 1) / 2) and ceil((n + 1) / 2): the same row for odd n, two rows for even n.
- Keep those row(s), then groupBy('department_id').agg(F.avg('salary')) and orderBy('department_id').
Interview practice
This problem is good practice for data engineering interviews at companies such as Amazon. Interviewers use problems like it to check whether you can express the logic cleanly and reason about correctness on edge cases such as ties, nulls and empty groups.
How to practise it on PySpark.in
Open the challenge, write your PySpark code in the editor and press Run to execute it against the sample dataset. Submitting checks your output against every test case, including hidden ones, so you find out straight away whether your logic holds up. You can retry as often as you like, and each solved challenge adds to your XP.
Related PYSPARK challenges
- Top 3 Products per Category
- Running Total Revenue
- Median Salary per Department
- Latest Order Per Customer
- 3-Day Rolling Sum of Sales
- 7-Day Rolling Purchase Amount by Customer
Helpful resources
Frequently asked questions
Do I need to install Spark or a database to solve this?
No. The PYSPARK environment runs in your browser with the sample data already loaded, so there is nothing to install or configure.
Is this challenge free?
Yes - the problem, the sample dataset, the hints and unlimited test runs are free.
What level is it?
It is rated hard and covers Window Functions.