Databricks Data Engineer Associate Question 192
Single answerYou have a table named sales_data with the following schema:
| product_id | region | sales |
|---|---|---|
| 101 | North | 500 |
| 101 | South | 300 |
| 102 | North | 400 |
| 102 | South | 600 |
You want to transform this table to show regions as columns and their corresponding sales values, resulting in the following structure:
| product_id | North | South |
|---|---|---|
| 101 | 500 | 300 |
| 102 | 400 | 600 |
Which of the following SQL queries will achieve this transformation?
- A
SELECT product_id, North, South FROM sales_data PIVOT (SUM(sales) FOR region IN ('North', 'South'))
- B
SELECT product_id, North, South FROM sales_data PIVOT (SUM(sales) FOR region IN ('North' AS North, 'South' AS South))
- C
SELECT product_id, North, South FROM (SELECT * FROM sales_data) PIVOT (SUM(sales) FOR region IN ('North', 'South'))
- D
SELECT product_id, North, South FROM (SELECT * FROM sales_data) PIVOT (COUNT(sales) FOR region IN ('North', 'South'))
Show answer and explanation
Correct answer: C
Explanation
The correct query uses the PIVOT clause to transform the sales_data table from a long format to a wide format, with regions as columns and their corresponding sales values. In Databricks, the PIVOT clause must be applied to a subquery or table expression, and the aggregation function (e.g., SUM) must match the desired result.
- A. Incorrect.
This query incorrectly applies the PIVOT clause directly on the table without wrapping the base query in a subquery, which is not valid syntax in Databricks.
- B. Incorrect.
This query introduces invalid syntax by attempting to alias values in the IN clause, which is not supported in the PIVOT clause.
- C. Correct.
This query correctly applies the PIVOT clause on a subquery, aggregating sales values using SUM() for each region and transforming the data into the desired wide format.
- D. Incorrect.
This query incorrectly uses the COUNT() function instead of SUM(), which would result in counting the number of rows per region rather than summing the sales values.