Failure streak that resets on success

PySpark data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 16 minutes. Part of the Pro drill bank.

Calculate running consecutive failure counts for each job run, resetting the streak to zero on every success. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

Given a DataFrame runs with columns job_id, run_id, and status ('FAIL' or 'SUCCESS'): For each job_id ordered by run_id: Calculate consecutive_failures: the current consecutive count of failure runs up to and including this run. When a run has status == 'SUCCESS', its consecutive_failures must be 0. When a failure follows a success, the failure streak starts over from 1. Columns to return: job_id, run_id, status, consecutive_failures. Sort by job_id, then run_id. Assign the final DataFrame to result.

Requirements

• Reset counter to 0 on any SUCCESS run.
• Sort by job_id, then run_id.

Constraints

• status is either 'FAIL' or 'SUCCESS'.
• run_id increments sequentially per job_id.

Examples

Input: runs job_id | run_id | status 1 | 1 | FAIL 1 | 2 | FAIL 1 | 3 | SUCCESS 1 | 4 | FAIL 1 | 5 | FAIL 1 | 6 | FAIL Output: job_id | run_id | status | consecutive_failures 1 | 1 | FAIL | 1 1 | 2 | FAIL | 2 1 | 3 | SUCCESS | 0 1 | 4 | FAIL | 1 1 | 5 | FAIL | 2 1 | 6 | FAIL | 3 The failure count builds to 2, drops to 0 upon success, and starts fresh from 1.

Topics: lakebench, pyspark, conditional-sum, run-length.

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