SQL Quest › SQL Interview Questions › NULL Handling

Fare Imputation Analysis

MediumFreeQuerying BasicsNULL HandlingAggregation & GroupingConditional LogicSubqueries & CTEs

Analyse Titanic fare data quality by class. For each pclass show: passengers_with_fare, passengers_missing_fare, avg_fare_known (actual average, NULLs excluded), and avg_fare_imputed (NULLs replaced with the overall fare average across all classes before computing). The two columns reveal what happens when you impute with the global mean — classes with below-average fares see their averages rise, classes with above-average fares see them fall. Order by pclass ascending.

Solve it in the browser editor →

Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect".

Schema

passengers

passenger_id	survived	pclass	name	sex	age	sibsp	fare	embarked
1	0	3	Braund, Mr. Owen Harris	male	22	1	7.25	S
2	1	1	Cumings, Mrs. John Bradley	female	38	1	71.28	C
3	1	3	Heikkinen, Miss. Laina	female	26	0	7.93	S

Expected output: Per-class fare completeness with raw vs globally-imputed averages

Hint

For avg_fare_imputed, use AVG(COALESCE(fare, (SELECT AVG(fare) FROM passengers WHERE fare IS NOT NULL))) — a non-correlated subquery returns the single overall fare mean, which you substitute for NULL fares. Note: if you impute with the CLASS mean instead of the overall mean, avg_fare_imputed will equal avg_fare_known by definition (adding the mean back into the mean doesn't change the mean).

Concepts

SELECT NULL Handling COALESCE GROUP BY Aggregation CASE Subquery

Practise the topic: SQL practice questions · NULL handling practice · GROUP BY exercises · CASE WHEN practice · CTE practice

In these company practice sets

Plaid · Snowflake

A SQL Quest challenge matched to patterns reported for these companies — not a question any of them has published.

Fare Imputation Analysis

Schema

Hint

Concepts

In these company practice sets

Related questions