Join Tables and Remove Duplicates | Dataford Interview Questions

Write a SQL query to join multiple tables and remove duplicate rows.

Use the provided customer, card, and transaction tables. For repeated transaction IDs, retain the most recently loaded record.

Output

One row per retained transaction with columns transaction_id, customer_id, customer_name, card_id, card_type, amount, and transaction_date
Exclude transactions whose card or customer cannot be matched
Order by transaction_date descending, then transaction_id ascending

customers

cards

Column	Type	Description
card_idPK	INT	Unique card identifier
customer_id	INT	Customer associated with the card
card_type	VARCHAR(40)	Card product type
card_status	VARCHAR(20)	Current card status

card_transactions

Column	Type	Description
transaction_record_idPK	INT	Unique ingestion record identifier
transaction_id	INT	Business transaction identifier
card_id	INT	Card used for the transaction
amount	DECIMAL(10,2)	Transaction amount
transaction_date	DATE	Date of the transaction
loaded_at	TIMESTAMP	Timestamp when the record was loaded

Your question is Join Tables and Remove Duplicates. 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.