Answer-first summary for fast verification
Answer: CREATE OR REPLACE VIEW sales_across_quarter AS SELECT year, region, q1, q2, q3, q4 FROM sales PIVOT (sum(sales) AS sales FOR quarter IN (1 AS q1, 2 AS q2, 3 AS q3, 4 AS q4));
The PIVOT clause is used to transform the rows of a table by rotating unique values of a specified column into separate columns. This is exactly what's needed to display sales data in separate columns for each quarter. The correct SQL command uses the PIVOT clause to aggregate sales by quarter and then rotates these into columns named q1, q2, q3, and q4 for each year and region. Syntax: ``` table_reference PIVOT ( { aggregate_expression [ [ AS ] agg_column_alias ] } [, …] FOR column_list IN ( expression_list ) ) ``` Where `column_list` is the set of columns to be rotated, and `expression_list` maps values from `column_list` to column aliases. The correct answer is the option that uses the PIVOT clause to achieve the desired transformation.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
A data engineer is tasked with transforming quarterly sales data across various geographical regions into a view where each quarter's sales are displayed in separate columns for every year. Given the schema and sample data of the sales table, which SQL command should be used to achieve this?
A
CREATE OR REPLACE VIEW sales_across_quarter AS SELECT year, region, q1, q2, q3, q4 FROM sales TRANSFORM (sum(sales) AS sales FOR quarter IN (1 AS q1, 2 AS q2, 3 AS q3, 4 AS q4));
B
CREATE OR REPLACE VIEW sales_across_quarter AS SELECT year, region, q1, q2, q3, q4 FROM sales PIVOT (sum(sales) AS sales FOR quarter IN (1 AS q1, 2 AS q2, 3 AS q3, 4 AS q4));
C
CREATE OR REPLACE VIEW sales_across_quarter AS SELECT year, region, q1, q2, q3, q4 FROM sales REDUCE (sum(sales) AS sales FOR quarter IN (1 AS q1, 2 AS q2, 3 AS q3, 4 AS q4));
D
CREATE OR REPLACE VIEW sales_across_quarter AS SELECT year, region, q1, q2, q3, q4 FROM sales EXIST(sum(sales) AS sales FOR quarter IN (1 AS q1, 2 AS q2, 3 AS q3, 4 AS q4));
E
CREATE OR REPLACE VIEW sales_across_quarter AS SELECT year, region, q1, q2, q3, q4 FROM sales ROTATE (sum(sales) AS sales FOR quarter IN (1 AS q1, 2 AS q2, 3 AS q3, 4 AS q4));
No comments yet.