Pivot total sales per department into columns (HR, IT, Sales).

Problem Statement

<p>Pivot total <code>sales</code> per department into columns (HR, IT, Sales).</p>

Examples

Input: sales table: +------------+--------+ | department | amount | +------------+--------+ | HR | 100 | | IT | 200 | | IT | 150 | | Sales | 300 | +------------+--------+

Output: +----------+----------+-------------+ | hr_sales | it_sales | sales_sales | +----------+----------+-------------+ | 100 | 350 | 300 | +----------+----------+-------------+

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

Complexity

Time Complexity: -

Space Complexity: -

Hints

💡 Hint 1: Use a conditional CASE WHEN ... THEN ... ELSE ... END expression to transform values or implement if-else logic. 💡 Hint 2: Each condition is evaluated in order; provide a sensible ELSE fallback if unhandled cases exist. 💡 Hint 3: Place the CASE expression in the SELECT list with an alias: SELECT CASE WHEN condition THEN val ELSE default END AS column_name FROM ...;

Editorial & Approach

Problem Overview & Intuition

To solve "Pivot Data using CASE", we query the relational database engine using declarative SQL. The goal is to pivot total sales per department into columns (hr, it, sales). 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: Ensure columns match the expected project schema in order.

Optimal Implementation (SQL)

SELECT SUM(CASE WHEN department = 'HR' THEN amount ELSE 0 END) AS hr_sales, SUM(CASE WHEN department = 'IT' THEN amount ELSE 0 END) AS it_sales, SUM(CASE WHEN department = 'Sales' THEN amount ELSE 0 END) AS sales_sales FROM sales;

Complexity Analysis

Time Complexity O(N)

Space Complexity O(1)

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.

Pivot Data using CASE

Hard

Pivot total sales per department into columns (HR, IT, Sales).

Example Scenarios

1Example 1

Input:

sales table

department	amount
HR	100
IT	200
IT	150
Sales	300

Output:

hr_sales	it_sales	sales_sales
100	350	300

Explanation:

The query retrieves the requested records satisfying all problem requirements.

SQL Editor

Loading Editor...

Query Results

Run a query to see results here.