Pivot Table

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

Widen product amounts per date with conditional aggregation (no DF.pivot). Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

A long table has 1 row for each date and product, like a list of sales. A pivot turns it into a wide table, with 1 row for each date and 1 column for each product, which is much easier to read. The PySpark in this editor has no built-in pivot, so you will build it by hand. From the long table df (with date, product and amount), create 1 row for each date with 3 columns A, B and C. Each column holds the sum of amount for that product on that date. If a product has no sales on a date, show 0. Sort by date and save the final table in result.

Requirements

• Return 1 row for each date.
• Create the columns A, B and C.
• Each of these holds the sum of amount for that product on that date.
• When a product has no rows on a date, the value is 0, not NULL.
• Sort by date.

Constraints

• The input is df with the columns date, product and amount.
• Do not use the pivot function. Build the columns with F.sum(F.when(...)).
• The only products are A, B and C.

Examples

Input: long product sales Output: date | A | B | C 2024-01-01 | 10.0 | 5.0 | 0.0 2024-01-02 | 7.0 | 0.0 | 3.0 2024-01-03 | 0.0 | 8.0 | 2.0 Missing product/date pairs become 0 via otherwise(0).

Topics: lakebench, pyspark, conditional aggregation, when.

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