12. Label Late Shipments

shipments has order_id, ordered_on and shipped_on (both 'YYYY-MM-DD'; shipped_on is null if not shipped yet).

Add two columns and return everything:

days_to_ship: days between ordering and shipping (null if not shipped)
status: 'not shipped' if there is no ship date, 'late' if it took more than 5 days, otherwise 'on time'

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

Sample data

shipments

order_id	ordered_on	shipped_on	days_to_ship	status
1	2025-03-01	2025-03-03	2	on time
2	2025-03-01	2025-03-09	8	late
3	2025-03-02	null	null	not shipped
4	2025-03-10	2025-03-15	5	on time

Hint 1

F.datediff(end, start) returns whole days; it is null when either date is null.

Hint 2

Check the null case first in your when chain: a null comparison is not true, so it would fall through to otherwise.

Hint 3

Exactly 5 days is still on time.

withColumn · 3 min read. Add or replace one column, and why calling it in a long loop slows Spark down.
when / otherwise · 3 min read. CASE WHEN logic inside a column: first match wins, and a missing otherwise means null.
trunc / add_months · 3 min read. Truncate dates to the month or year and shift them by whole months, including month-end rules.