Items per user as an array

PySpark data engineering interview problem. Difficulty: intermediate. Pattern: Aggregation. About 14 minutes. Part of the Pro drill bank.

Per user, the distinct items as a sorted array, their count, and whether a gift card is among them. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

You want to see all items each user has bought in a single row. The table purchases has user and item. Some items are NULL, and users sometimes buy the same item more than once. Return 1 row for each user with 3 columns. items is the list of distinct items, sorted alphabetically, and NULL items are left out. item_count is the number of distinct items. has_gift_card is true when the list contains gift_card. Return user, items, item_count and has_gift_card. The editor creates the table purchases and ends with result = purchases. Change the last line to build your answer. The functions collect_set, sort_array, size and array_contains will help.

Requirements

• Group by user and collect the distinct items with F.collect_set.
• Sort the array alphabetically with F.sort_array.
• Add item_count as the size of the array and has_gift_card with F.array_contains.
• Return user, items, item_count, has_gift_card in result.

Constraints

• collect_set removes duplicates and ignores NULL values.
• A set has no order, so the sort is needed to make the output stable.
• A user whose items are all NULL would get an empty array and a count of 0.

Examples

Input: purchases user | item u1 | book u1 | pen u1 | book u1 | gift_card u2 | pen u2 | NULL u3 | mug u3 | mug Output: user | items | item_count | has_gift_card u1 | ["book","gift_card","pen"] | 3 | True u2 | ["pen"] | 1 | False u3 | ["mug"] | 1 | False u1 bought book twice but it is listed once; u2's NULL item is ignored; u3's repeated mug gives a single item.

Topics: lakebench, pyspark, collect_set, array, array_contains.

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