Unpivot (Melt) Table

PySpark data engineering interview problem. Difficulty: beginner. Pattern: Pivot. About 12 minutes. Free to practice.

Melt wide product columns into long date/product/amount rows via union. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

This is the reverse of the pivot problem. The table wide has 1 row for each date and a column for each product (product_A, product_B, product_C). Many tools prefer a long table with 1 row for each date and product. Turning columns into rows is called unpivoting or melting. Build the long table with the columns date, product and amount, where product is 'A', 'B' or 'C'. Do not keep rows where the amount is 0. Sort by date and then by product. There is no built-in melt here. Create one small table for each product column, and glue them together with union. Save the final table in result.

Requirements

• Return the columns date, product and amount.
• Create 1 row for each date and product, with product set to 'A', 'B' or 'C'.
• Leave out rows where amount is 0.
• Sort by date and then by product.

Constraints

• The input is wide with the columns date, product_A, product_B and product_C.
• Use union of three selects. Do not use stack or melt.
• Use F.lit('A') to put the product name in as a constant column.

Examples

Input: wide product columns Output: date | product | amount 2024-01-01 | A | 10.0 2024-01-01 | B | 5.0 2024-01-02 | A | 7.0 2024-01-02 | C | 3.0 2024-01-03 | B | 8.0 2024-01-03 | C | 2.0 Each non-zero wide cell becomes one long row.

Topics: lakebench, pyspark, union, melt.

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