Department Monthly Revenue Pivot Report

Easy

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 1

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 2

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 3

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.

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

[ { "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 } ]

[ { "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 } ]

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

[ { "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 } ]