SQL and Database Operations | Dataford Interview Questions

Explain how you would perform common SQL database operations for an i2c payment or banking workflow.

Write a PostgreSQL query that summarizes January 2025 settled debit activity for every active account and includes accounts with no qualifying transactions. Join account ownership information, exclude non-settled or non-debit activity, and handle missing transaction amounts safely.

Output

One row per active account, with account_id, customer_name, total_debit_amount, settled_debit_count, and latest_transaction_date
Include accounts with no qualifying transactions, using zero totals and a NULL latest date
Sort by total_debit_amount descending, then account_id ascending

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer display name

accounts

Column	Type	Description
account_idPK	INT	Unique account identifier
customer_id	INT	Owning customer identifier
account_type	VARCHAR(30)	Type of banking account
currency	VARCHAR(3)	ISO currency code
account_status	VARCHAR(20)	Current account status

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
account_id	INT	Account affected by the transaction
transaction_type	VARCHAR(20)	Debit or credit classification
status	VARCHAR(20)	Processing status
amount	NUMERIC(12,2)	Transaction amount
transaction_date	DATE	Transaction posting date

Your question is SQL and Database Operations. 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