Question

A data engineer has a Silver table 'order_items' with one row per line item, containing columns 'order_id', 'product_name', and 'quantity'. For a reporting requirement, they must produce a Gold table with exactly one row per 'order_id' where a new column 'products' holds an array of all 'product_name' values for that order. Which Spark SQL approach produces the correct result?

Accepted Answer

SELECT order_id, collect_list(product_name) AS products FROM order_items GROUP BY order_id

Answer

SELECT order_id, explode(product_name) AS products FROM order_items GROUP BY order_id

Answer

SELECT order_id, array(product_name) AS products FROM order_items

Answer

SELECT DISTINCT order_id, product_name AS products FROM order_items