Unpivot wide sales to rows

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

This is the opposite of a pivot. The table sales_wide has 1 row for each product and a separate column for each month (jan, feb, mar). Many tools prefer a long table with 1 row for each product and month. Turn the wide table into a long one with the columns product_id, month and revenue, where month is exactly 'Jan', 'Feb' or 'Mar'. Sort by product_id, and inside each product, in calendar order (Jan, then Feb, then Mar).

Requirements

• Create 1 row for each product and month.
• The month column holds the text 'Jan', 'Feb' or 'Mar'.
• The revenue column holds the value from the matching month column.
• Sort by product_id, and then by month in calendar order, not alphabetical order.

Constraints

• The table is sales_wide with the columns product_id, jan, feb and mar.
• Use UNION ALL of three selects, one for each month column.
• Return only these 3 columns, in this order: product_id, month, revenue.

Examples

Input: sales_wide product_id | jan | feb | mar P1 | 100 | 120 | 90 Output: product_id | month | revenue P1 | Jan | 100 P1 | Feb | 120 P1 | Mar | 90 Why this passes: Each wide column becomes a row with a month label.

Topics: lakebench, sql, unpivot.

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