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.
Overview: 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.
The finance analytics team needs a report that shows each department in one row with separate revenue columns for every month of the year and an annual total.
Given table DEPARTMENT_REVENUE(Department, Revenue, Month), where Month is a three-letter abbreviation from 'Jan' to 'Dec', 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. Months with no revenue for a department should appear as 0. Also include an Annual_Total column that is the sum of that department's revenue across all months.
Tables
DEPARTMENT_REVENUE(Department VARCHAR(10), Revenue INTEGER, Month VARCHAR(3))
Hints
- Use conditional aggregation with SUM and CASE to build each month column.
- Use ELSE 0 in the CASE expression (or wrap SUM with COALESCE) so missing months appear as 0.