29. Consecutive Status Runs (Gaps and Islands)
Difficulty: Hard · Topics: Window Functions, Aggregations
server_status has exactly one row per server per day with that day's status ('up' or 'down').
Collapse consecutive days with the same status into runs. Return server, status, start_day, end_day and days (length of the run).
Row order does not matter; column names must match. Your code is graded on 3 test cases, including hidden edge cases.
Sample data
server_status
| server | day | status |
|---|---|---|
| web1 | 2024-01-01 | up |
| web1 | 2024-01-02 | up |
| web1 | 2024-01-03 | down |
| web1 | 2024-01-04 | up |
| web1 | 2024-01-05 | up |
| db1 | 2024-01-01 | down |
| db1 | 2024-01-02 | down |
Expected output
| server | status | start_day | end_day | days |
|---|---|---|---|---|
| web1 | up | 2024-01-01 | 2024-01-02 | 2 |
| web1 | down | 2024-01-03 | 2024-01-03 | 1 |
| web1 | up | 2024-01-04 | 2024-01-05 | 2 |
| db1 | down | 2024-01-01 | 2024-01-02 | 2 |
Hints
Hint 1
Number each server's days in order, and separately number days within each (server, status).Hint 2
Inside one run both numbers go up together, so their difference is constant, and it changes when a run ends.Hint 3
Group by server, status and that difference; take min/max day and the count.PySpark functions you'll practise
- Row Number
- Window spec
- groupBy
- agg
- count
- min
- max
- orderBy
Related problems
- Three-Day Login Streak · Hard · Window Functions
- Word Count · Medium · Arrays
- New Users per Day · Medium · Aggregations
- Fill Missing Dates (Calendar Spine) · Hard · Joins
- Monthly Retention by Cohort · Hard · Joins
Browse
Topics: Window Functions · Joins · Aggregations · Pivot, Unpivot & Rollup · Arrays · Null Handling · Conditional Logic · Dates · Filtering & Selection · Strings
Difficulty: Easy · Medium · Hard · PySpark interview roadmap · All problems