Convert column-based quarterly data into row-based data.

Problem Statement

<p>Convert column-based quarterly data into row-based data.</p>

Examples

Input: quarterly_sales table: +---------+-----+-----+-----+-----+ | product | q1 | q2 | q3 | q4 | +---------+-----+-----+-----+-----+ | Widget | 100 | 150 | 200 | 175 | | Gadget | 80 | 120 | 90 | 110 | +---------+-----+-----+-----+-----+

Output: +---------+---------+-------+ | product | quarter | sales | +---------+---------+-------+ | Gadget | Q1 | 80 | | Gadget | Q2 | 120 | | Gadget | Q3 | 90 | | Gadget | Q4 | 110 | | Widget | Q1 | 100 | | Widget | Q2 | 150 | | Widget | Q3 | 200 | | Widget | Q4 | 175 | +---------+---------+-------+

Explanation: The query retrieves the requested records satisfying all problem requirements.

Complexity

Time Complexity: -

Space Complexity: -

Hints

💡 Hint 1: Combine the results of two or more queries using UNION ALL (preserves duplicates). 💡 Hint 2: Ensure both SELECT queries have the same number of columns in the same order and compatible data types. 💡 Hint 3: Structure: SELECT ... FROM table1 UNION [ALL] SELECT ... FROM table2;

Editorial & Approach

Problem Overview & Intuition

To solve "Unpivot Data using UNION ALL", we query the relational database engine using declarative SQL. The goal is to convert column-based quarterly data into row-based data. By formulating an optimal execution plan with appropriate projection and filtering, the database engine executes the query with minimal overhead.

Step-by-Step Approach

Analyze Schema: Identify the target tables, necessary foreign keys, and expected output columns.
Construct Filtering & Logic: Apply row projections to isolate the requested data.
Format & Order: Sort the resulting records according to specified order criteria.

Optimal Implementation (SQL)

SELECT product, 'Q1' AS quarter, q1 AS sales FROM quarterly_sales UNION ALL SELECT product, 'Q2', q2 FROM quarterly_sales UNION ALL SELECT product, 'Q3', q3 FROM quarterly_sales UNION ALL SELECT product, 'Q4', q4 FROM quarterly_sales ORDER BY product, quarter;

Complexity Analysis

Time Complexity O(N log N) for sorting or partitioning rows.

Space Complexity O(N) for intermediate group hash tables or window buffers.

Key Considerations & Edge Cases

Empty Tables: The query executes safely returning zero rows without syntax error.
NULL Values: Columns containing NULL values are properly handled by standard ANSI SQL semantics.
Case Sensitivity: String comparisons and keywords adhere to PostgreSQL/standard SQL rules.

Unpivot Data using UNION ALL

Hard

Convert column-based quarterly data into row-based data.

Example Scenarios

1Example 1

Input:

quarterly_sales table

product	q1	q2	q3	q4
Widget	100	150	200	175
Gadget	80	120	90	110

Output:

product	quarter	sales
Gadget	Q1	80
Gadget	Q2	120
Gadget	Q3	90
Gadget	Q4	110
Widget	Q1	100
Widget	Q2	150
Widget	Q3	200
Widget	Q4	175

Explanation:

The query retrieves the requested records satisfying all problem requirements.

SQL Editor

Loading Editor...

Query Results

Run a query to see results here.