SQL Queries and Procedures | Dataford Interview Questions

Describe your experience with SQL querying, stored procedures, and triggers.

Demonstrate your approach using the provided PostgreSQL tables. Include a query that summarizes completed transactions, a stored procedure that records a transaction, and a trigger that audits transaction inserts.

Output

Return one row per wallet with wallet_id, wallet_name, transaction_count, total_amount, last_transaction_at, and audit_event_count.
Include wallets without completed transactions, treat missing totals and counts as zero, and order by wallet_id ascending.

Schema

wallets

Column	Type	Description
wallet_idPK	INT	Unique wallet identifier
wallet_name	VARCHAR(100)	Wallet holder name
currency_code	VARCHAR(3)	ISO currency code
current_balance	NUMERIC(12,2)	Current wallet balance
wallet_status	VARCHAR(20)	Current wallet status

wallet_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
wallet_id	INT	Wallet associated with the transaction
amount	NUMERIC(12,2)	Transaction amount
status	VARCHAR(20)	Transaction processing status
transaction_at	TIMESTAMP	Transaction timestamp

transaction_audit

Column	Type	Description
audit_idPK	INT	Unique audit event identifier
transaction_id	INT	Audited transaction identifier
wallet_id	INT	Wallet associated with the audit event
event_type	VARCHAR(20)	Audit event type
event_at	TIMESTAMP	Audit event timestamp

Your question is SQL Queries and Procedures. Start with the requirements and the three 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