Gold revenue behind a quality gate

PySpark data engineering interview problem. Difficulty: intermediate. Pattern: Medallion. About 18 minutes. Part of the Pro drill bank.

Publish daily revenue only for days where enough rows have an amount, and hold the rest. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

silver has 1 row per order (silver is the cleaned layer, and gold is the layer built from it for reports): day, order_id and amount. amount is missing for some orders. Build the daily gold table with a quality gate. For each day return: rows: orders that day rows_with_amount: orders that have an amount valid_share: rows_with_amount divided by rows, rounded to 2 decimals gate: pass when at least 80% of the day's orders have an amount, otherwise hold revenue: the sum of amount for a day that passes, missing for a day on hold Decide gate from the exact share, not the rounded one. Sort by day. Assign the DataFrame to result.

Requirements

• Return the columns day, rows, rows_with_amount, valid_share, gate, revenue.
• Sort by day.

Constraints

• Every day has at least one order.

Examples

Input: silver day | order_id | amount 2024-06-01 | 1 | 10 2024-06-01 | 2 | 20 2024-06-01 | 3 | 5 2024-06-01 | 4 | 15 2024-06-01 | 5 | NULL 2024-06-02 | 6 | 8 2024-06-02 | 7 | NULL 2024-06-02 | 8 | NULL 2024-06-02 | 9 | 12 2024-06-03 | 10 | 30 2024-06-03 | 11 | 40 2024-06-03 | 12 | 50 2024-06-04 | 13 | NULL Output: day | rows | rows_with_amount | valid_share | gate | revenue 2024-06-01 | 5 | 4 | 0.8 | pass | 50 2024-06-02 | 4 | 2 | 0.5 | hold | NULL 2024-06-03 | 3 | 3 | 1 | pass | 120 2024-06-04 | 1 | 0 | 0 | hold | NULL 1 June has 4 of 5 amounts, exactly 80%, so it passes. 2 June has 2 of 4 and is held, 3 June is complete, and 4 June has none.

Topics: lakebench, pyspark, medallion, gold, quality gate, aggregation.

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