Create Monthly Revenue Report for Each Department
Company: Amazon
Role: Business Intelligence Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Onsite
DEPARTMENT_REVENUE
+------------+---------+-------+
| Department | Revenue | Month |
+------------+---------+-------+
| A | 8000 | Jan |
| B | 9000 | Jan |
| C | 10000 | Feb |
| A | 7000 | Feb |
| A | 6000 | Mar |
+------------+---------+-------+
##### Scenario
Finance analytics team needs a report that shows each department in one row with separate revenue columns for every month of the year.
##### Question
Given table DEPARTMENT_REVENUE(Department, Revenue, Month), write an SQL query that pivots the data so the output has one row per department and twelve columns: Jan_Revenue, Feb_Revenue, …, Dec_Revenue.
##### Hints
Use conditional aggregation (CASE) or database-specific PIVOT operator.
Quick Answer: This question evaluates proficiency in SQL data transformation and aggregation, specifically the ability to pivot row-level transactional revenue into a columnar monthly report and handle schema alignment for reporting.