Transactions in Data Systems | Dataford Interview Questions

Explain what a transaction is and why it matters in a system that processes concurrent requests.

Using the transaction and request-operation records, write a SQL query that produces a concise summary for each recorded transaction. Include transactions with no recorded requests. Requests without a matching transaction should not appear in any transaction summary.

Output

One row per transaction with transaction_id, isolation_level, status, request_count, failed_request_count, and duration_ms.
Treat every response status other than 200, including NULL, as failed.
Sort by duration_ms descending, then transaction_id ascending.

Schema

database_transactions

Column	Type	Description
transaction_idPK	INT	Unique database transaction identifier
isolation_level	VARCHAR(30)	Isolation level used by the transaction
status	VARCHAR(30)	Final transaction status
started_at	TIMESTAMP	Transaction start timestamp
ended_at	TIMESTAMP	Transaction completion timestamp

request_operations

Column	Type	Description
request_idPK	INT	Unique request identifier
transaction_id	INT	Associated database transaction identifier
operation	VARCHAR(30)	Operation performed by the request
response_status	VARCHAR(50)	Response status returned by the operation

Your question is Transactions in Data Systems. 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