Null-Safe Aggregation — Solution & ExplanationProblems / Null-Safe Aggregation / EditorialApproach

Group by product_id.

AVG(rating) computes the mean of the non-NULL ratings only — SQL aggregates silently skip NULLs. Round to 2 decimal places.

COUNT(rating) counts only non-NULL ratings, while COUNT(*) counts all rows in the group. So:

rated_count = COUNT(rating)

missing_count = COUNT(*) - COUNT(rating)

Order by product_id.

Key InsightCOUNT(*) vs COUNT(column) is one of the most common interview traps. COUNT(*) counts rows; COUNT(column) counts non-NULL values in that column. Every other aggregate (AVG, SUM, MIN, MAX) also skips NULLs, which is why AVG(rating) divides by COUNT(rating) — not the row count — and why a product with zero rated reviews yields NULL, not 0. If you ever need the "treat missing as 0" behavior instead, you must opt in explicitly with AVG(COALESCE(rating, 0)).In PySpark the same distinction shows up as F.count("rating") (skips NULLs) vs F.count(F.lit(1)) (all rows).Hints

COUNT(*) counts rows; COUNT(rating) counts only rows where rating is NOT NULL. The gap between those two numbers is exactly the missing count.

AVG(rating) already ignores NULLs — it sums the non-NULL values and divides by COUNT(rating), not COUNT(*). No CASE expression needed.

A product whose ratings are ALL NULL has COUNT(rating) = 0 and AVG(rating) = NULL. Leave it as NULL — coercing it to 0 would invent a rating that nobody gave.

In PySpark, F.count("rating") skips NULLs while F.count(F.lit(1)) counts every row, so missing_count is their difference.

Solve Null-Safe Aggregation yourself →