Reformat Department Table - Practice Coding Problems

Reformat Department Table - Problem

Database Easy

You're a data analyst working with a company's monthly revenue data. The finance team has provided you with a database table that stores each department's revenue for different months in a normalized format (each row represents one department-month combination).

Your task is to pivot this data into a more readable format where each department appears as a single row with separate columns for each month's revenue.

Input Format:
A table named Department with the following structure:

Column Name	Type
id	int
revenue	int
month	varchar

Where (id, month) is the primary key, and month contains values from ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"].

Output Format:
Transform the table so that there's one row per department with columns for id and each month's revenue (Jan_Revenue, Feb_Revenue, etc.).

Input & Output

example_1.sql — Basic Example

$ Input: Department table: | id | revenue | month | |----|---------|-------| | 1 | 8000 | Jan | | 2 | 9000 | Jan | | 3 | 10000 | Feb | | 1 | 7000 | Feb | | 1 | 6000 | Mar |

› Output: | id | Jan_Revenue | Feb_Revenue | Mar_Revenue | Apr_Revenue | ... | Dec_Revenue | |----|-------------|-------------|-------------|-------------|-----|-------------| | 1 | 8000 | 7000 | 6000 | null | ... | null | | 2 | 9000 | null | null | null | ... | null | | 3 | null | 10000 | null | null | ... | null |

💡 Note: Each department ID becomes a single row with separate columns for each month's revenue. Missing month data shows as NULL.

example_2.sql — Single Department

$ Input: Department table: | id | revenue | month | |----|---------|-------| | 1 | 8000 | Jan | | 1 | 7500 | Jun | | 1 | 9200 | Dec |

› Output: | id | Jan_Revenue | Feb_Revenue | ... | Jun_Revenue | ... | Dec_Revenue | |----|-------------|-------------|-----|-------------|-----|-------------| | 1 | 8000 | null | ... | 7500 | ... | 9200 |

💡 Note: Department 1 has revenue data for only 3 months (Jan, Jun, Dec), other months show NULL.

example_3.sql — Empty Table

$ Input: Department table: | id | revenue | month | |----|---------|-------| (empty table)

› Output: | id | Jan_Revenue | Feb_Revenue | Mar_Revenue | Apr_Revenue | ... | Dec_Revenue | |----|-------------|-------------|-------------|-------------|-----|-------------| (no rows)

💡 Note: When the input table is empty, the pivoted result is also empty.

Visualization

Tap to expand

Understanding the Visualization

Original Data Structure

Each row represents one department's revenue for one specific month

Group by Department

SQL groups all rows belonging to the same department ID

Apply Conditional Logic

CASE WHEN statements check each row's month and assign revenue to appropriate column

Aggregate Results

SUM function combines matching values (only one value per department-month due to primary key constraint)

Key Takeaway

🎯 Key Insight: SQL's CASE WHEN combined with GROUP BY enables efficient row-to-column transformation in a single table scan, making it the optimal approach for pivoting data.

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through the table with GROUP BY aggregation

✓ Linear Growth

Space Complexity

O(d)

Space for d departments in the result set

✓ Linear Space

Constraints

1 ≤ Department table rows ≤ 1000
1 ≤ id ≤ 100
0 ≤ revenue ≤ 10⁶
month is one of ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]
(id, month) forms the primary key - no duplicate combinations

Asked in

a Amazon 15 ⊞ Microsoft 12 f Meta 8 G Google 6

The optimal solution uses CASE WHEN with GROUP BY to pivot the data in a single table scan. By combining conditional logic with aggregation functions, we transform rows into columns efficiently with O(n) time complexity.

Common Approaches

Approach	Time	Space	Notes
✓ CASE WHEN with GROUP BY (Optimal)	O(n)	O(d)	Use conditional aggregation to pivot data in a single table scan
Multiple Subqueries (Brute Force)	O(n * m)	O(n)	Use separate subqueries for each month column

CASE WHEN with GROUP BY (Optimal) — Algorithm Steps

Group all records by department ID
Use CASE WHEN to conditionally sum revenue for each month
Apply SUM aggregation to get one value per month per department
Handle NULL values automatically through aggregation

Visualization

Tap to expand

Step-by-Step Walkthrough

Scan Table Once

Read each row and apply CASE conditions

Group by ID

Aggregate rows by department ID

Apply Conditions

CASE WHEN selects revenue for matching months

SUM Aggregation

Combine values for each department-month combination

Code -

solution.c — C

// Optimal SQL query using CASE WHEN with GROUP BY
char* query = "\
SELECT \
    id, \
    SUM(CASE WHEN month = 'Jan' THEN revenue END) AS Jan_Revenue, \
    SUM(CASE WHEN month = 'Feb' THEN revenue END) AS Feb_Revenue, \
    SUM(CASE WHEN month = 'Mar' THEN revenue END) AS Mar_Revenue, \
    SUM(CASE WHEN month = 'Apr' THEN revenue END) AS Apr_Revenue, \
    SUM(CASE WHEN month = 'May' THEN revenue END) AS May_Revenue, \
    SUM(CASE WHEN month = 'Jun' THEN revenue END) AS Jun_Revenue, \
    SUM(CASE WHEN month = 'Jul' THEN revenue END) AS Jul_Revenue, \
    SUM(CASE WHEN month = 'Aug' THEN revenue END) AS Aug_Revenue, \
    SUM(CASE WHEN month = 'Sep' THEN revenue END) AS Sep_Revenue, \
    SUM(CASE WHEN month = 'Oct' THEN revenue END) AS Oct_Revenue, \
    SUM(CASE WHEN month = 'Nov' THEN revenue END) AS Nov_Revenue, \
    SUM(CASE WHEN month = 'Dec' THEN revenue END) AS Dec_Revenue \
FROM Department \
GROUP BY id \
ORDER BY id";

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through the table with GROUP BY aggregation

✓ Linear Growth

Space Complexity

O(d)

Space for d departments in the result set

✓ Linear Space

Constraints

1 ≤ Department table rows ≤ 1000
1 ≤ id ≤ 100
0 ≤ revenue ≤ 10⁶
month is one of ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]
(id, month) forms the primary key - no duplicate combinations

23.4K Views

Medium Frequency

~15 min Avg. Time

892 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

CASE WHEN with GROUP BY (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler