Department Monthly Revenue Pivot Report

EASY10 pts

You are given a long-form finance table where each row stores one monthly revenue figure for one department.

Table: department

id: department identifier
revenue: revenue value recorded for a month
month: one of Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec

Data guarantees:

(id, month) is unique.
A department can appear for any subset of months.
Some months may be missing for a department.

Task:

Convert the long-form table into a wide monthly report.
Return one row per id.
Create one revenue column for each month: Jan_Revenue through Dec_Revenue.
If a month is missing for a department, return NULL for that month column.

Output column order must be exactly:

id
Jan_Revenue, Feb_Revenue, Mar_Revenue, Apr_Revenue, May_Revenue, Jun_Revenue, Jul_Revenue, Aug_Revenue, Sep_Revenue, Oct_Revenue, Nov_Revenue, Dec_Revenue

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

department:
| id | revenue | month |
|----|---------|-------|
| 1  | 8000    | Jan   |
| 2  | 9000    | Jan   |
| 3  | 10000   | Feb   |
| 1  | 7000    | Feb   |
| 1  | 6000    | Mar   |

Output

[
  {
    "id":1,
    "Jan_Revenue":8000,
    "Feb_Revenue":7000,
    "Mar_Revenue":6000,
    "Apr_Revenue":null,
    "May_Revenue":null,
    "Jun_Revenue":null,
    "Jul_Revenue":null,
    "Aug_Revenue":null,
    "Sep_Revenue":null,
    "Oct_Revenue":null,
    "Nov_Revenue":null,
    "Dec_Revenue":null
  },
  {
    "id":2,
    "Jan_Revenue":9000,
    "Feb_Revenue":null,
    "Mar_Revenue":null,
    "Apr_Revenue":null,
    "May_Revenue":null,
    "Jun_Revenue":null,
    "Jul_Revenue":null,
    "Aug_Revenue":null,
    "Sep_Revenue":null,
    "Oct_Revenue":null,
    "Nov_Revenue":null,
    "Dec_Revenue":null
  },
  {
    "id":3,
    "Jan_Revenue":null,
    "Feb_Revenue":10000,
    "Mar_Revenue":null,
    "Apr_Revenue":null,
    "May_Revenue":null,
    "Jun_Revenue":null,
    "Jul_Revenue":null,
    "Aug_Revenue":null,
    "Sep_Revenue":null,
    "Oct_Revenue":null,
    "Nov_Revenue":null,
    "Dec_Revenue":null
  }
]

Explanation

Each (id, month) value is routed into the corresponding month column. Months with no row for that id become null.

Example

Input

department:
| id | revenue | month |
|----|---------|-------|
| 10 | 100     | Dec   |
| 10 | 0       | Jan   |
| 10 | 250     | Aug   |
| 20 | 300     | Apr   |

Output

[
  {
    "id":10,
    "Jan_Revenue":0,
    "Feb_Revenue":null,
    "Mar_Revenue":null,
    "Apr_Revenue":null,
    "May_Revenue":null,
    "Jun_Revenue":null,
    "Jul_Revenue":null,
    "Aug_Revenue":250,
    "Sep_Revenue":null,
    "Oct_Revenue":null,
    "Nov_Revenue":null,
    "Dec_Revenue":100
  },
  {
    "id":20,
    "Jan_Revenue":null,
    "Feb_Revenue":null,
    "Mar_Revenue":null,
    "Apr_Revenue":300,
    "May_Revenue":null,
    "Jun_Revenue":null,
    "Jul_Revenue":null,
    "Aug_Revenue":null,
    "Sep_Revenue":null,
    "Oct_Revenue":null,
    "Nov_Revenue":null,
    "Dec_Revenue":null
  }
]

Explanation

The input can be unordered. Revenue 0 is a valid value and must not be replaced by null.

Example

Input

department:
| id   | revenue | month |
|------|---------|-------|
| 101  | 4000    | Jun   |
| 101  | 4100    | Jul   |
| 103  | 9500    | Jun   |
| 103  | 9600    | Jul   |
| 103  | 9700    | Aug   |

Output

[
  {
    "id":101,
    "Jan_Revenue":null,
    "Feb_Revenue":null,
    "Mar_Revenue":null,
    "Apr_Revenue":null,
    "May_Revenue":null,
    "Jun_Revenue":4000,
    "Jul_Revenue":4100,
    "Aug_Revenue":null,
    "Sep_Revenue":null,
    "Oct_Revenue":null,
    "Nov_Revenue":null,
    "Dec_Revenue":null
  },
  {
    "id":103,
    "Jan_Revenue":null,
    "Feb_Revenue":null,
    "Mar_Revenue":null,
    "Apr_Revenue":null,
    "May_Revenue":null,
    "Jun_Revenue":9500,
    "Jul_Revenue":9600,
    "Aug_Revenue":9700,
    "Sep_Revenue":null,
    "Oct_Revenue":null,
    "Nov_Revenue":null,
    "Dec_Revenue":null
  }
]

Explanation

Each department can have a different subset of months. The result is a fixed 13-column report for all ids.

Accepted0/0·0% Acceptance

Constraints

0 <= number of rows in department <= 50000
1 <= id <= 1000000000
0 <= revenue <= 1000000000
month is one of: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
(id, month) is unique
Output must contain exactly 13 columns
Output column order must match the specification
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with the required columns

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

department =

[{"id":1,"revenue":8000,"month":"Jan"},{"id":2,"revenue":9000,"month":"Jan"},{"id":3,"revenue":10000,"month":"Feb"},{"id":1,"revenue":7000,"month":"Feb"},{"id":1,"revenue":6000,"month":"Mar"}]

Department Monthly Revenue Pivot Report

Hints

Department Monthly Revenue Pivot Report

Hints