Context

Analytics datasets often contain missing values, blank strings, duplicate records, and inconsistent categories. If these issues are not handled carefully, reports and downstream metrics become unreliable.

Question

Explain how you would handle NULL values and dirty data when preparing a dataset for analytics in SQL. Your answer should cover:

1. How to distinguish true NULLs from other dirty values such as empty strings, placeholder text, or invalid numbers
2. When to use filtering versus standardization versus default replacement
3. How functions like COALESCE, NULLIF, CASE WHEN, and aggregates behave with missing data
4. How your cleaning choices can affect business metrics such as counts, averages, and conversion rates

Scope Guidance

Keep the discussion practical and SQL-focused. The interviewer expects you to explain both the reasoning and the trade-offs, not just list functions. Use short PostgreSQL examples where helpful.