NA, Null, and NaN Handling in SQL | Dataford Interview Questions

Difference between NA, Null and NaN, and how to deal with them in SQL

Asked in the Written SQL round stage. Write a PostgreSQL query that classifies raw text values and summarizes their quality by category. Treat SQL NULL, the text markers NA and N/A, the text marker NaN, valid numeric strings, and other text separately.

Output

One row per category, including categories with no matching values.
Columns: category, total_values, null_count, na_count, nan_count, valid_numeric_count, invalid_count.
Order by category ascending.

Schema

categories

Column	Type	Description
record_idPK	INT	Identifier for the category record
category	VARCHAR(50)	Product category

raw_values

Column	Type	Description
value_idPK	INT	Identifier for the raw value
record_id	INT	References categories.record_id
raw_value	TEXT	Uncleaned value represented as text

Your question is NA, Null, and NaN Handling in SQL. 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