SQL Window Functions for Transactions | Dataford Interview Questions

Write a SQL query using window functions to find each T-Mobile customer's most recent transaction and the previous transaction amount.

Use the provided customer and transaction data. Include customers with at least one transaction, and use transaction timestamp plus transaction ID to break ties.

Output

One row per customer with at least one transaction.
Columns: customer_id, customer_name, transaction_id, transaction_timestamp, most_recent_transaction_amount, and previous_transaction_amount.
Order by customer_id ascending. Return NULL when no previous transaction exists.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique T-Mobile customer identifier
customer_name	VARCHAR(100)	Customer name

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
customer_id	INT	Customer who made the transaction
transaction_timestamp	TIMESTAMP	Timestamp when the transaction occurred
amount	DECIMAL(10,2)	Transaction amount in US dollars

Your question is SQL Window Functions for Transactions. 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