Pivot monthly sales to columns

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Pivot. About 12 minutes. Part of the Pro drill bank.

A report is easier to read when each month has its own column, instead of 1 row for each month. Turning rows into columns like this is called a pivot. The table sales has 1 row for each product and month, with the columns product_id, month and revenue. The months are written as 'Jan', 'Feb' and 'Mar'. Return 1 row for each product with the columns product_id, jan_revenue, feb_revenue and mar_revenue. Sort by product_id.

Requirements

• Return 1 row for each product_id.
• Each month column holds the revenue of that month for the product.
• Group by product_id and use SUM(CASE WHEN month = '...' THEN revenue END) for each month.
• Sort by product_id.

Constraints

• The table is sales with the columns product_id, month and revenue.
• Month values are exactly 'Jan', 'Feb' and 'Mar' (title case, 3 letters).
• Return only these 4 columns, in this order: product_id, jan_revenue, feb_revenue, mar_revenue.

Examples

Input: sales product_id | month | revenue P1 | Jan | 100 P1 | Feb | 120 P1 | Mar | 90 Output: product_id | jan_revenue | feb_revenue | mar_revenue P1 | 100 | 120 | 90 Why this passes: Conditional aggregation turns month rows into columns.

Topics: lakebench, sql, pivot, case.

More SQL interview questions · All interview problems · Learn data engineering