Question

A data engineer has a Silver table 'monthly_sales' with columns product_id, month (values 'Jan', 'Feb', 'Mar'), and revenue. For a Gold reporting table, they need one row per product_id with separate columns for each month's total revenue. Which Spark SQL approach produces this result most directly?

Accepted Answer

SELECT * FROM monthly_sales PIVOT (SUM(revenue) FOR month IN ('Jan', 'Feb', 'Mar'))

Answer

SELECT product_id, month, SUM(revenue) FROM monthly_sales GROUP BY product_id, month

Answer

SELECT product_id, EXPLODE(revenue) FROM monthly_sales GROUP BY product_id

Answer

SELECT product_id, COLLECT_LIST(revenue) FROM monthly_sales GROUP BY product_id