Contiguous Dates
ASKED IN INTERVIEWSaa S
Problem Statement
Find streaks of consecutive dates and group them into batches.
Example:
If dates are Jan 1, Jan 2, Jan 4, Jan 5:
- Batch 1: Jan 1 to Jan 2
- Batch 2: Jan 4 to Jan 5
Rules:
- Output: batch_id, start_date, end_date.
- Sort by start_date ascending.
Tests your understanding of
Gaps and Islands, Date Math and Window Functions
Input Tables
task_logs
| log_id(INTEGER) | execution_date(DATE) |
|---|---|
| 1 | 2026-01-01 |
| 2 | 2026-01-02 |
| 3 | 2026-01-04 |
| 4 | 2026-01-05 |
| 5 | 2026-01-07 |
tasks
| task_id(INTEGER) | task_name(VARCHAR) |
|---|---|
| 1 | Backups |
batch_settings
| id(INTEGER) | max_gap(INTEGER) |
|---|---|
| 1 | 0 |
Expected Output
| batch_id(INTEGER) | start_date(DATE) | end_date(DATE) |
|---|---|---|
| 1 | 2026-01-01 | 2026-01-02 |
| 2 | 2026-01-04 | 2026-01-05 |
| 3 | 2026-01-07 | 2026-01-07 |
Tags
HardASKED IN INTERVIEWGaps and IslandsDate MathWindow Functions
30-40 min
31%