13. Order Lines with Product Details

Difficulty: Easy · Topics: Joins, Filtering & Selection

order_lines has order_id, product_id and qty. products has product_id, name and price.

Return order_id, name, qty and line_total (qty × price) for every order line whose product exists in products. Lines with an unknown product are left out.

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

Sample data

order_lines

order_id	product_id	qty
100	1	2
100	2	1
101	3	5
102	9	1

products

product_id	name	price
1	Notebook	50
2	Pen	10
3	Stapler	250

Expected output

order_id	name	qty	line_total
100	Notebook	2	100
100	Pen	1	10
101	Stapler	5	1250

Hints

Hint 1

An inner join keeps only lines whose product matches.

Hint 2

Join on the column name, "product_id", so the result has a single key column.

Hint 3

Compute line_total after the join, when price is available.

Learn the concepts

• join · 4 min read. Combine DataFrames on a key: inner, left, right and full joins, and the duplicate-key fan-out trap.
• select · 4 min read. Choose, rename and compute columns, and why select beats a chain of withColumn calls.

PySpark functions you'll practise

• join
• select

Related problems

• Customers Without Orders · Medium · Joins
• Employees and Their Departments · Medium · Joins
• Year-over-Year Growth · Medium · Joins
• Employees Earning More than Their Manager · Medium · Joins
• Joining on Nullable Keys · Medium · Joins

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 · Learn · All problems