First and Last Transaction | Dataford Interview Questions

Write a SQL query to find the first and last transaction for each customer.

Return customers with no transactions as well, using the transaction timestamp for chronological order. If a customer has multiple transactions at the same timestamp, use the transaction ID as the tie-breaker.

Output

One row per customer, including customers without transactions.
Columns: customer_id, customer_name, first_transaction_id, first_transaction_at, last_transaction_id, last_transaction_at.
Sort by customer_id ascending.

Schema

customers

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

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
customer_id	INT	Customer associated with the transaction
transaction_at	TIMESTAMPTZ	Timestamp when the transaction occurred
amount	NUMERIC(12,2)	Transaction amount

Your question is First and Last Transaction. 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