Weighted average: SUM(x*w)/SUM(w)

None of the six dialects have a built-in weighted-average aggregate — AVG() weights every row equally. The formula is the same everywhere: multiply each value by its weight, sum both, divide.

-- portable in all six dialects
SELECT
  product_id,
  SUM(rating * review_count) / SUM(review_count) AS weighted_avg_rating
FROM product_reviews_by_store
GROUP BY product_id;

If review_count can sum to zero for a group (e.g. a product with only unrated reviews), the division errors instead of returning NULL. Guard it with NULLIF:

SELECT
  product_id,
  SUM(rating * review_count) / NULLIF(SUM(review_count), 0) AS weighted_avg_rating
FROM product_reviews_by_store
GROUP BY product_id;

Two dialects also ship a dedicated safe-division function you can use instead:

• BigQuery: SAFE_DIVIDE(SUM(rating * review_count), SUM(review_count)) — returns NULL on divide-by-zero in one call, no NULLIF needed.
• Snowflake: DIV0NULL(SUM(rating * review_count), SUM(review_count)) returns NULL on a zero denominator; DIV0(...) returns 0 instead if that’s the behavior you want.

Why NULLIF(x, 0) works. NULLIF(a, b) returns NULL when a = b, otherwise a. So NULLIF(SUM(review_count), 0) turns a zero sum into NULL — and dividing by NULL yields NULL (not an error) in every one of these six dialects. A NULL weight column is a separate issue: SUM() already skips NULLs and returns NULL for an all-NULL group on its own, so the guard specifically matters for a denominator that sums to a literal 0.