Optimize High-Latency Query | Dataford Interview Questions

How would you optimize a database query that is experiencing high latency during peak usage?

Using the provided schema and representative data, rewrite the query to report active endpoints with completed requests during the specified peak interval. Keep the result focused on endpoints that recorded at least one slow request.

Output

One row per qualifying endpoint with endpoint_id, endpoint_name, request_count, avg_latency_ms, and slow_request_count.
Include only active endpoints with completed requests in the interval and at least one request at or above 500 ms.
Order by slow_request_count descending, avg_latency_ms descending, then endpoint_id ascending.

Schema

endpoints

Column	Type	Description
endpoint_idPK	INT	Unique endpoint identifier
endpoint_name	VARCHAR(100)	Endpoint name
is_active	BOOLEAN	Whether the endpoint is currently active

query_events

Column	Type	Description
event_idPK	INT	Unique query event identifier
endpoint_id	INT	Endpoint associated with the event
occurred_at	TIMESTAMP	Timestamp when the request occurred
status	VARCHAR(20)	Request completion status
latency_ms	INT	Request latency in milliseconds

Your question is Optimize High-Latency Query. Start with the requirements and the two tables in the Question tab.

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

Problem