Rank vs Dense Rank

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

Show rank, dense_rank, and row_number per user by amount desc. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

When values tie, there are three ways to number them. rank() gives tied rows the same number and then skips (1, 1, 3). dense_rank() gives tied rows the same number and does not skip (1, 1, 2). row_number() never ties and gives every row a different number (1, 2, 3). For each user_id, order the rows by amount from high to low and then by ts. Add 3 columns: rank_r, dense_r and row_r. Select user_id, amount, ts and the three new columns, sorted by user_id and row_r. The editor creates the table df and ends with result = df. Change the last line to build your answer.

Requirements

• Use one window partitioned by user_id, ordered by amount descending and then ts.
• Add rank_r, dense_r and row_r using rank, dense_rank and row_number.
• Select user_id, amount, ts, rank_r, dense_r, row_r.
• Sort by user_id, row_r and store the DataFrame in result.

Constraints

• All three ranking columns use the same window.
• The ts column is in the output and is also the tie-breaker in the order.
• Amounts are decimal numbers, so two amounts of 40.0 are equal.

Examples

Input: tied amounts for user 1 Output: user_id | amount | ts | rank_r | dense_r | row_r 1 | 40.0 | 2024-01-01 10:00:00 | 1 | 1 | 1 1 | 40.0 | 2024-01-01 11:00:00 | 1 | 1 | 2 1 | 10.0 | 2024-01-01 09:00:00 | 3 | 2 | 3 Each user's eligible order totals are aggregated; users with higher totals appear earlier in the ranked output.

Topics: lakebench, pyspark, rank, dense_rank, row_number.

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