SQL Data Quality Checks | Dataford Interview Questions

Sigmoid's transaction pipelines need a repeatable SQL audit before data is loaded into downstream analytics surfaces. Write a PostgreSQL query that returns every transaction with one or more data quality issues.

Requirements

Flag missing customer_id, merchant_id, amount, currency, or status, treating empty customer IDs as missing.
Flag duplicate business keys defined by (merchant_id, external_transaction_id).
Flag non-positive amounts, transactions after 2025-01-31, and transaction rows whose merchant does not exist.
Return one row per affected transaction with a comma-separated, alphabetically ordered list of issue types, sorted by transaction_id.

Schema

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction row identifier
merchant_id	INT	Referenced merchant identifier
external_transaction_id	VARCHAR(30)	Source-system transaction key
customer_id	VARCHAR(30)	Customer identifier
amount	NUMERIC(12,2)	Transaction amount
currency	VARCHAR(3)	Currency code
transaction_date	DATE	Transaction date
status	VARCHAR(20)	Processing status

merchants

Column	Type	Description
merchant_idPK	INT	Merchant identifier
merchant_name	VARCHAR(100)	Merchant display name

Your question is SQL Data Quality Checks. 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