D8LooPFocus modeCODE WORKSPACE
Late arriving event audit
Return event_day and late_events: the count of events whose ingestion date is after their event date, grouped by event day. Order by event_day.
Filter date(ingested_at) > date(event_time), then GROUP BY date(event_time).
Sample input
eventsEvent 3 (event day 01-01) and event 5 (event day 01-02) were ingested on a later date.
| event_id | event_time | ingested_at |
|---|---|---|
| 1 | 2026-01-01 03:30:00 | 2026-01-01 03:31:00 |
| 3 | 2026-01-01 22:15:00 | 2026-01-02 08:00:00 |
| 5 | 2026-01-02 05:00:00 | 2026-01-04 09:00:00 |
| 6 | 2026-01-03 18:00:00 | 2026-01-03 18:05:00 |
Expected output
Expected outputLate events attributed to their event day. On-time days do not appear.
| event_day | late_events |
|---|---|
| 2026-01-01 | 1 |
| 2026-01-02 | 1 |
Constraints
Keep only rows where date(ingested_at) > date(event_time), then GROUP BY date(event_time) with COUNT(*) AS late_events. Order by event_day.
Expected skills
Lateness logic and attributing late events to their event day for a completeness check.
SQL
Loading...
AI evaluation
Run the SQL query to inspect preview rows.