Summarizing Transactions with SQL | Dataford Interview Questions

Write a SQL query using joins and aggregations to summarize transaction data by customer.

Use the customers and transactions tables. Include customers with no transactions. Treat only transactions with status completed as successful, and treat NULL amounts as zero for successful-amount calculations.

Output

One row per customer, including customers without transactions.
Columns: customer_id, customer_name, total_transactions, successful_transactions, successful_amount, and last_transaction_date.
Sort by successful_amount descending, then customer_id ascending.

Schema

customers

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

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
customer_id	INT	Customer associated with the transaction
transaction_date	DATE	Date the transaction occurred
status	VARCHAR(20)	Transaction processing status
amount	DECIMAL(12,2)	Transaction amount

Your question is Summarizing Transactions with SQL. 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