Longest attendance streak

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

For each employee, the longest run of consecutive calendar days marked present. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

You want to know the longest run of days in a row on which an employee was present. The table attendance has employee, day (a date as text) and present (1 or 0). A day with no row at all is not a present day. For each employee, return longest_streak, the length of the longest run of consecutive calendar days on which present = 1. An employee who was never present has a streak of 0. Return employee and longest_streak. The editor creates the table attendance and ends with result = attendance. Change the last line to build your answer. A well-known trick: for consecutive days, the date number minus the row number stays the same, so it can be used as a group label.

Requirements

• Keep only the rows with present = 1.
• Group consecutive days together by using the date number minus the row number.
• Count the days in each group and take the largest for each employee.
• Include employees who were never present, with a streak of 0.
• Return employee and longest_streak in result.

Constraints

• Consecutive means one calendar day after the other. A missing day breaks the streak.
• Use datediff to turn the date into a number, and row_number over the days of each employee.
• Join back to all employees and fill empty streaks with 0.

Examples

Input: attendance employee | day | present e1 | 2024-05-01 | 1 e1 | 2024-05-02 | 1 e1 | 2024-05-03 | 1 e1 | 2024-05-04 | 0 e1 | 2024-05-05 | 1 e1 | 2024-05-06 | 1 e2 | 2024-05-01 | 1 e2 | 2024-05-03 | 1 e2 | 2024-05-04 | 1 e3 | 2024-05-02 | 0 Output: employee | longest_streak e1 | 3 e2 | 2 e3 | 0 e1 has runs of 3 and 2 days, so 3. e2 is present on May 1, then May 3 and 4: the longest run is 2. e3 was never present.

Topics: lakebench, pyspark, gaps and islands, row_number, datediff.

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