
Explanation:
The correct answer involves using ARRAY_UNION and COLLECT_SET. COLLECT_SET is an aggregate function that combines column values from all rows into a unique list, while ARRAY_UNION combines arrays and removes any duplicates. This combination effectively creates a unique list of items per cartId.
Ultimate access to all questions.
No comments yet.
Given a dataset with two columns, cartId (session ID) and items (an array of items added to the cart), the Marketing team requests a unique list of items ever added to the cart by each customer. Complete the query to achieve the expected result.
Schema: cartId INT, items Array
Sample Data:
SELECT cartId, ___ (___(items)) as items
FROM carts GROUP BY cartId
SELECT cartId, ___ (___(items)) as items
FROM carts GROUP BY cartId
Expected result:
cartId items
1 [1,100,200,300,250]
cartId items
1 [1,100,200,300,250]
A
FLATTEN, COLLECT_UNION
B
ARRAY_UNION, FLATTEN
C
ARRAY_UNION, ARRAY_DISTINCT
D
ARRAY_UNION, COLLECT_SET
E
ARRAY_DISTINCT, ARRAY_UNION