Table: Department
+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | revenue | int | | month | varchar | +-------------+---------+ In SQL,(id, month) is the primary key of this table. The table has information about the revenue of each department per month. The month has values in ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"].
Reformat the table such that there is a department id column and a revenue column for each month.
Return the result table in any order.
The result format is in the following example.
Example 1:
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 | ... | Dec_Revenue | +------+-------------+-------------+-------------+-----+-------------+ | 1 | 8000 | 7000 | 6000 | ... | null | | 2 | 9000 | null | null | ... | null | | 3 | null | 10000 | null | ... | null | +------+-------------+-------------+-------------+-----+-------------+ Explanation: The revenue from Apr to Dec is null. Note that the result table has 13 columns (1 for the department id + 12 for the months).
This approach involves using a pivot table strategy to reformat the data. The key is to transform row data of each month's revenue into separate columns for each month. We will iterate through the table, grouping revenues by department id and arranging them into columns corresponding to each month.
This solution leverages pandas to pivot the DataFrame. The pivot operation uses the 'id' as index and 'month' as columns, transforming the revenue data into month-column format. Finally, the columns are renamed to include the '_Revenue' suffix, and the result is returned in dictionary format.
JavaScript
Time Complexity: O(n), where n is the number of rows in the input table.
Space Complexity: O(n), for storing the pivoted DataFrame.
This approach focuses on iterating over the data, storing information using an aggregation map or dictionary. Each department will have its own dictionary that maps months to revenue, allowing us to easily access and format this data into the desired output structure.
This Java solution uses a HashMap to collect month-specific revenue data for each department. After populating this map, it constructs the output list by iterating over departments, ensuring that all months are represented, inserting null where revenue data is absent.
C++
Time Complexity: O(n * m), where n is the number of departments and m is the number of months.
Space Complexity: O(n * m), with m fixed at 12, for storing department-month mappings.
| Approach | Complexity |
|---|---|
| Pivot Table Approach | Time Complexity: O(n), where n is the number of rows in the input table. |
| Data Aggregation Approach | Time Complexity: O(n * m), where n is the number of departments and m is the number of months. |
LeetCode 1179: Reformat Department Table [SQL] • Frederik Müller • 7,012 views views
Watch 9 more video solutions →Practice Reformat Department Table with our built-in code editor and test cases.
Practice on FleetCode