Cleaning a Messy Dataset for a High-Stakes Report

Describe a time you had to clean a large, messy dataset to produce a high-stakes report. What was your process?

Using the supplied tables, write a query that produces a cleaned regional report. Exclude records with missing dates, non-positive amounts, non-completed statuses, or stores absent from the directory.

Output

One row per directory region, including regions with no valid sales
Columns: region, valid_transaction_count, total_sales, average_sale, and invalid_record_count
Sort by region ascending

Schema

sales_records

Column	Type	Description
record_idPK	INT	Unique raw sales record identifier
store_code	VARCHAR(10)	Store identifier supplied by the source system
sale_date	DATE	Date on which the sale was recorded
amount	DECIMAL(12,2)	Reported sale amount
status	VARCHAR(30)	Raw transaction status
customer_email	VARCHAR(150)	Customer email captured with the record

store_directory

Column	Type	Description
store_codePK	VARCHAR(10)	Official store identifier
region	VARCHAR(40)	Reporting region for the store

Your question is Cleaning a Messy Dataset for a High-Stakes Report. 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