Late-arriving telemetry facts

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Late Data. About 25 minutes. Part of the Pro drill bank.

In a streaming pipeline, an event can reach the warehouse minutes or hours after it really happened. A small delay is normal, but a big delay can break reports that were already published. The data team wants a list of the late rows, so they can choose a good watermark (the point after which they stop waiting for late data).

From telemetry, return the rows that arrived late: where the time between event_time and ingest_time is more than 15 minutes. For each of those rows, return device_id, metric, event_time, ingest_time and lag_minutes. lag_minutes is a whole number, the difference in minutes from event_time to ingest_time, calculated with date_diff('minute', event_time, ingest_time). Order by lag_minutes from the largest to the smallest, then by reading_id.

Requirements

• Calculate lag_minutes with date_diff('minute', event_time, ingest_time).
• Return only rows where lag_minutes is greater than 15.
• Return device_id, metric, event_time, ingest_time and lag_minutes.
• Order by lag_minutes descending, then reading_id.

Constraints

• Rows that arrived within 15 minutes are left out completely.
• A lag of exactly 15 minutes is not included, because the rule says more than 15.
• reading_id is used for ordering but is not part of the output.

Examples

Input: device_id | metric | event_time | ingest_time DEV-026 | current_a | 2026-08-08 07:34:30 | 2026-08-08 09:13:30 DEV-010 | vibration_mm | 2026-08-03 16:06:18 | 2026-08-03 17:44:18 Output: device_id | metric | event_time | ingest_time | lag_minutes DEV-026 | current_a | 2026-08-08 07:34:30 | 2026-08-08 09:13:30 | 99 DEV-010 | vibration_mm | 2026-08-03 16:06:18 | 2026-08-03 17:44:18 | 98 Why this passes: the output matches the required columns, grain, and conditions for this problem.

Topics: watermarks, late data.

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