Drill 3 of 12 in Data engineering questions
0

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…