HardSQL challenge #300AGGREGATIONSTATISTICSANALYSIS

Calculate Rating Variance per Product Without a VARIANCE Function

Return each product with the variance of its review ratings, most consistent first.

The challenge runs on the SQLabHub practice database (SQLite) and uses the reviews table. You can solve it in the browser, with nothing to install.

Solve This SQL Challenge

The task

Find products with minimal variance in rating.

A correct answer returns 93 rows with 2 columns: product_id, variance.

Tables used in this challenge

reviews 180 rows

id INTEGER (PK) · product_id INTEGER · user_id INTEGER · rating INTEGER · comment TEXT · reviewed_at TEXT · helpful_votes INTEGER

First 3 rows of the reviews table
idproduct_iduser_idratingcommentreviewed_athelpful_votes
123455Works perfectly. Very happy with this purchase.2022-09-0240
2101685Excellent build quality. Will buy again!2023-01-0596
340391Would give 6 stars if I could!2023-05-1517

The first rows of each table are shown. See how all the tables relate on the database schema page.

Expected output

Expected output of the challenge
product_idvariance
10
30
40
50
80

First 5 of 93 rows. This is the output of the model solution on the practice database.

What you practise

  • Summarising many rows into one value with COUNT, SUM, AVG, MIN or MAX
  • Grouping rows with GROUP BY to get one result row per group
  • Sorting the result with ORDER BY
  • Deriving a new column from an expression and naming it with an alias

Hint and approach

Try the challenge first. Open these only when you need them.

Show the hint

variance logic

Show the step-by-step approach

How the model solution is built, in the order the database evaluates it:

  1. Read from the reviews table.
  2. Group the rows by product_id, so the result has one row for each value.
  3. For each group, compute AVG(rating * rating) and AVG(rating).
  4. Calculate variance from those aggregates: AVG(rating * rating) - AVG(rating)*AVG(rating).
  5. Sort the result by variance.

Model solution

Checking your session…

What to watch for

SQLite has no VARIANCE or STDDEV. Population variance equals the mean of the squares minus the square of the mean: AVG(x * x) - AVG(x) * AVG(x). A product with a single review has a variance of 0, so those sort first.

  • GROUP BY. Every selected column must either be in GROUP BY or sit inside an aggregate. SQLite accepts other columns and returns an arbitrary value for them; most databases raise an error.
  • Calculated columns. Dividing one integer by another truncates the result in SQLite. Multiply one side by 1.0 to get decimals.
  • ORDER BY. Without ORDER BY the row order is not guaranteed, even if it looks stable. Rows that tie on the sort columns can come back in any order.
Solve This SQL Challenge

Same technique, other tables

More practice on this pattern with different columns:

Keep learning