Handle Missing Data Across Retail Datasets | Dataford Interview Questions

Describe how you handle missing or inconsistent data when joining multiple large-scale retail datasets at dm-drogerie markt.

Use the provided sales, product, and store tables to demonstrate the approach with a query. Normalize join keys, preserve every sales record, and identify whether each product and store matched successfully.

Output

One row per sales transaction, ordered by transaction_id ascending.
Include transaction_id, product_id, product_name, store_id, store_name, product_join_status, store_join_status, and sales_amount.
Label each join as matched or missing, treating NULL and blank keys as missing.

Schema

retail_sales

Column	Type	Description
transaction_idPK	INT	Unique retail transaction identifier
raw_product_code	VARCHAR(20)	Product reference received from the sales feed
raw_store_code	VARCHAR(20)	Store reference received from the sales feed
sales_amount	DECIMAL(10,2)	Transaction amount, including returns and missing values

products

Column	Type	Description
product_idPK	INT	Canonical product identifier
product_code	VARCHAR(20)	Canonical product code
product_name	VARCHAR(100)	Product description
category	VARCHAR(50)	Product category

stores

Column	Type	Description
store_idPK	INT	Canonical store identifier
store_code	VARCHAR(20)	Canonical store code
store_name	VARCHAR(100)	Store name
region	VARCHAR(50)	Store region

Your question is Handle Missing Data Across Retail Datasets. 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