SQL: Timed Out Activity IDs | Dataford Interview Questions

Your question is SQL: Timed Out Activity IDs. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Google Cloud Workflows records each execution of an activity, including retries. Write a PostgreSQL query that returns the IDs of activities whose latest execution has timed out as of 2025-02-01 12:00:00.

An execution is timed out when it is the latest execution for its activity, its status is running or timed_out, its completed_at is NULL, and the activity deadline is at or before the evaluation timestamp.

Requirements

Use a CTE and ROW_NUMBER() to identify the latest execution for each activity by started_at.
Join the latest executions to activities and apply the timeout conditions.
Return only activity_id, ordered ascending.
Exclude activities with no execution, completed executions, future deadlines, failed executions, and executions with a NULL deadline.

Schema

activities

Column	Type	Description
activity_idPK	INTEGER	Unique activity identifier
activity_name	VARCHAR(100)	Human-readable activity name
timeout_at	TIMESTAMP	Deadline for the activity execution

activity_executions

Column	Type	Description
execution_idPK	INTEGER	Unique execution identifier
activity_id	INTEGER	Referenced activity
status	VARCHAR(20)	Execution state
started_at	TIMESTAMP	Execution start timestamp
completed_at	TIMESTAMP	Completion timestamp, if completed