Join with Multiple Conditions

PySpark data engineering interview problem. Difficulty: beginner. Pattern: Joins. About 12 minutes. Free to practice.

Join on user_id then keep orders above the user's average. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

Each user has an average order amount stored in the users table. You want to find the orders that are bigger than that user's own average. Join orders to users on user_id, then keep only the rows where orders.amount is greater than users.avg_order_amount. Select order_id, user_id, amount, name and avg_order_amount, and sort by order_id. The editor creates both tables and ends with result = orders. Change the last line so result holds your answer. Join first so both numbers are on the same row, and then filter.

Requirements

• Join orders and users on user_id.
• Keep only rows where amount is greater than avg_order_amount.
• Select order_id, user_id, amount, name, avg_order_amount.
• Sort by order_id and store the DataFrame in result.

Constraints

• avg_order_amount is a column in users, so there is nothing to calculate.
• Use a strict comparison: an amount equal to the average is not above it.
• Both columns are on the same row after the join, so the filter can use F.col("amount") > F.col("avg_order_amount").

Examples

Input: orders vs avg_order_amount Output: order_id | user_id | amount | name | avg_order_amount 102 | 1 | 120.0 | Ada | 60.0 104 | 2 | 90.0 | Alan | 50.0 Equality join plus a residual inequality filter.

Topics: lakebench, pyspark, join, filter.

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