Percentile Rank

PySpark data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 16 minutes. Part of the Pro drill bank.

percent_rank of amount within each user_id. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

A percent rank tells you where a value sits in its group, as a number from 0 to 1. The smallest value gets 0, the largest gets 1, and the others are in between. It is calculated as the number of rows below the current row divided by the number of other rows, or (rank - 1) / (n - 1). For each user_id, compute percentile_rank of amount, with the smallest amount first. Return user_id, amount and percentile_rank, sorted by user_id and amount. The editor creates the table df and ends with result = df. Change the last line to build your answer. Use F.percent_rank() over a window.

Requirements

• Use a window partitioned by user_id and ordered by amount ascending.
• Add percentile_rank with F.percent_rank().over(w).
• Return user_id, amount, percentile_rank, sorted by user_id, amount.
• Store the DataFrame in result.

Constraints

• The result is a decimal number from 0.0 to 1.0.
• The smallest amount of every user has percentile_rank 0.0.
• Do not use rank(), which gives whole numbers like 1, 2, 3.

Examples

Input: three amounts per user Output: user_id | amount | percentile_rank 1 | 10.0 | 0.0 1 | 20.0 | 0.5 1 | 30.0 | 1.0 percent_rank is (rank-1)/(n-1) inside the partition.

Topics: lakebench, pyspark, percent_rank.

More PySpark interview questions · All interview problems · Learn data engineering