26. Percent Rank and Cumulative Distribution

Difficulty: Medium · Topics: Window Functions

Within each dept, compute for every employee:

• pct_rank: percent_rank() by salary ascending,
• cume_dist: the fraction of the department earning the same or less.

Round both to 2 decimals. Return name, dept, salary, pct_rank, cume_dist.

Row order does not matter; column names must match. Your code is graded on 3 test cases, including hidden edge cases.

Sample data

employees

name	dept	salary
Ana	eng	100
Bo	eng	120
Cy	eng	120
Di	eng	150
Ed	ops	80
Flo	ops	95

Expected output

name	dept	salary	pct_rank	cume_dist
Ana	eng	100	0	0.25
Bo	eng	120	0.33	0.75
Cy	eng	120	0.33	0.75
Di	eng	150	1	1
Ed	ops	80	0	0.5
Flo	ops	95	1	1

Hints

Hint 1

Both are window functions over Window.partitionBy("dept").orderBy("salary").

Hint 2

percent_rank = (rank − 1) / (rows − 1); a one-person department gets 0.

Hint 3

cume_dist counts ties (peers) together: equal salaries get the same value.

PySpark functions you'll practise

• Percent Rank
• Cumulative Distribution
• Window spec
• orderBy

Related problems

• Latest Order per Customer · Medium · Window Functions
• Running Total by Region · Medium · Window Functions
• Top Two Salary Levels per Department · Medium · Window Functions
• Month-over-Month Change · Medium · Window Functions
• Three-Day Login Streak · Hard · Window Functions

Browse

Topics: Window Functions · Joins · Aggregations · Pivot, Unpivot & Rollup · Arrays · Null Handling · Conditional Logic · Dates · Filtering & Selection · Strings

Difficulty: Easy · Medium · Hard · PySpark interview roadmap · All problems