Write a Basic Join and Group By | Dataford Interview Questions

Write a basic JOIN query across multiple tables, then a GROUP BY query.

Asked in the Technical Round 1 stage. Use the customers, accounts, and transactions tables. Include customers without accounts or posted transactions, and consider only transactions whose status is posted.

Output

One row per customer with customer_id, customer_name, account_count, posted_transaction_count, and total_posted_amount.
Include every customer, with zeroes where no matching records exist.
Order by customer_id ascending.

Schema

customers

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

accounts

Column	Type	Description
account_idPK	INT	Unique account identifier
customer_id	INT	Customer who owns the account
account_type	VARCHAR(30)	Type of account

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
account_id	INT	Account associated with the transaction
status	VARCHAR(20)	Transaction processing status
amount	NUMERIC(12,2)	Transaction amount

Your question is Write a Basic Join and Group By. 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