Handling Missing Data in Reports | Dataford Interview Questions

How do you handle missing or inconsistent data when preparing a report for leadership at TikTok Shop?

For this exercise, treat only completed and shipped orders with nonnegative amounts and a matched seller as valid. Return seller-level results, including an Unmapped seller bucket for orders with missing or unmatched seller IDs.

Output

One row per seller represented in the order data, plus Unmapped seller
Columns: seller_name, order_count, valid_gmv, and invalid_order_count
Count every order, sum only valid amounts, and order by seller_name ascending

Schema

sellers

Column	Type	Description
seller_idPK	INT	Unique seller identifier
seller_name	VARCHAR(100)	Seller display name

shop_orders

Column	Type	Description
order_idPK	INT	Unique order identifier
seller_id	INT	Seller associated with the order
status	VARCHAR(30)	Order lifecycle status
amount	NUMERIC(12,2)	Order amount used for GMV validation

Your question is Handling Missing Data in Reports. 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