Context

At NovaCart, monthly retention reporting depends on reliable transaction dates. If transaction records have missing dates, retention metrics can be understated, overstated, or assigned to the wrong month.

Question

Explain how you would clean and prepare a transaction table with missing customer transaction dates for a monthly retention analysis. Your answer should cover:

1. How to identify and assess missing or invalid transaction dates
2. What rules you would use to impute, exclude, or flag affected rows
3. How to transform cleaned transaction data into customer-month activity suitable for retention analysis
4. How you would validate that the cleaned dataset does not distort retention results

Scope Guidance

The interviewer expects a practical SQL-focused explanation, not just a definition. Discuss data quality checks, trade-offs in handling missing dates, and how the cleaned output should support downstream monthly cohort or repeat-activity analysis.