Interview Question Bank · LEFT JOIN with an ON filter · MAX · interval vs SLA
3. Stale tables
EasyData engineeringQuestion #243
Concept: LEFT JOIN with an ON filter · MAX · interval vs SLAshow
Freshness is the time since a table's last successful load. Filter to successful runs inside the LEFT JOIN's ON clause, not in WHERE: a WHERE on the joined table throws away tables with no matching run, which are the stalest of all. Subtracting timestamps gives an interval, and EXTRACT(EPOCH FROM interval) / 3600 turns it into hours.
Example (different data):
SELECT s.sensor, MAX(r.read_at) AS last_ok, ROUND(EXTRACT(EPOCH FROM TIMESTAMP '2025-01-01 00:00' - MAX(r.read_at)) / 3600, 1) AS hours_quiet FROM sensors s LEFT JOIN readings r ON r.sensor = s.sensor AND r.ok GROUP BY s.sensor;
Your task
Fernhill's data platform posts a freshness report at noon. Each table in de_tables has an SLA in hours, and every load attempt is logged in de_load_runs.
As of 2025-06-10 12:00, return every table in de_tables with table_name, last_success_at (finished_at of its latest successful run, NULL if none), hours_since_load (hours from last_success_at to 2025-06-10 12:00, rounded to 1 decimal) and is_stale (true when the table has never loaded successfully or its last success is more than freshness_sla_hours old). Sort by table_name.
Starting Postgres in your browser…
Checking your access…