Articles

Weighted Average SQL Interview Questions: Join Grain, Missing Weights, and Correct Totals

Prepare for weighted average SQL interview questions with tested examples on join grain, missing weights, correct totals, numeric types, and rolling windows.

A weighted average SQL answer can contain the right formula and still calculate the wrong metric. A join repeats some observations, a missing rating remains in the denominator, or a dashboard averages regional results without considering their sizes. Each query runs. Each result looks plausible.

Start with Compute weighted response rates by job category as a companion exercise. It uses pandas, but its check that a weighted mean matches aggregated counts transfers directly to SQL.

Evidence boundary: official database documentation supports the SQL behavior described below. Sources were checked on September 15, 2026.

First explain what the weight means

For eligible observations, a weighted mean is SUM(value * weight) / SUM(weight). The denominator represents the total amount of whatever the weight measures. It is not automatically a row count.

Suppose two orders have ratings 5 and 1. The first contains two units; the second contains eight. An order-weighted rating is (5 + 1) / 2 = 3. A unit-weighted rating is (5*2 + 1*8) / 10 = 1.8. Both are mathematically valid. They answer different questions.

Before writing SQL, establish four things:

Observation: one order, one item, one customer, or one daily summary?
Weight: units, revenue, exposure time, invitations, or a statistical adjustment?
Eligibility: which missing, zero, invalid, or refunded records count?
Output: which groups must appear, how undefined results display, and when rounding occurs?

Inference from the metric definition: weighting an order-level rating by units treats that rating as representative of every eligible unit in the order. SQL cannot establish whether this is a sensible business assumption. If the request is simply “average customer satisfaction,” clarify it rather than silently choosing units.

A six-order example with deliberate data problems

For this original exercise, calculate the unit-weighted order rating by region. Ratings are nullable integers from 1 through 5. Each item row belongs to one order. Quantities should be nonnegative integers; a null quantity means the order's total weight is unknown.

The compact fixture uses these two relations:

orders(order_id, region, rating)
101  East  5
102  East  1
103  West  4
104  West  NULL
105  West  2
106  West  3

items(line_id, order_id, quantity)
1  101  1
2  101  1
3  102  8
4  103  2
5  104  4
6  105  NULL
7  106  0

Our contract excludes orders with missing ratings, unknown weights, or zero weight. Any negative item quantity makes that order's weight invalid. Returns would need a separate policy; they are not silently interpreted as negative survey weights.

The eligible orders are 101, 102, and 103. East contributes numerator 18 and weight 10. West contributes numerator 8 and weight 2. The excluded orders remain important data-quality evidence even though they do not contribute to this metric.

A useful answer names those exclusions before presenting the final average. Otherwise, another analyst cannot tell whether West's result describes one rated order or every order in that region.

Aggregate weights to the observation grain before joining

Documented SQL behavior: a join produces a result for each matching pair of rows. An order can therefore appear several times when joined to its items. PostgreSQL table expressions.

This query builds one weight per order, joins it once to the order's rating, and filters both parts of the ratio using the same eligibility rule:

WITH order_weights AS (
    SELECT order_id,
           CASE
             WHEN COUNT(*) = COUNT(quantity)
              AND MIN(quantity) >= 0
             THEN SUM(1.0 * quantity)
           END AS units
    FROM items
    GROUP BY order_id
), eligible AS (
    SELECT o.order_id, o.region, o.rating, w.units
    FROM orders o
    JOIN order_weights w ON w.order_id = o.order_id
    WHERE o.rating IS NOT NULL
      AND w.units > 0
), totals AS (
    SELECT region,
           SUM(rating * units) AS numerator,
           SUM(units) AS denominator
    FROM eligible
    GROUP BY region
)
SELECT region, numerator, denominator,
       numerator / NULLIF(denominator, 0) AS weighted_rating
FROM totals
ORDER BY region;

The output is East (18, 10, 1.8) and West (8, 2, 4.0). The numeric values matter; a particular client may display trailing zeroes differently.

COUNT(*) = COUNT(quantity) prevents an unknown item quantity from being silently omitted when computing a supposedly complete order weight. MIN(quantity) >= 0 rejects an order containing a negative quantity, even if its net quantity would be positive. An order with no item rows never enters order_weights and is excluded here.

This version returns only regions with eligible observations. If the prompt requires every region, start the final presentation from a region dimension, left join the totals, and retain a null average for regions without eligible weight. That is an output-population requirement, not a reason to invent a zero rating.

Why join duplication does not always cancel

A frequent explanation says that duplication is harmless because it multiplies both the numerator and denominator. That is true if every eligible observation is multiplied by the same factor. A one-to-many join generally does not promise that.

In East, joining the order-level weights back to item rows repeats order 101 twice but order 102 once. If the query reuses each order's whole weight on every joined row, it calculates (5*2*2 + 1*8) / (2*2 + 8) = 28/12, about 2.3333. The correct answer is 1.8.

By contrast, using each individual item's quantity with the repeated order rating can be correct: each line contributes its own share of the weight. The problem is mixing a repeated order-level total with item-level rows, not the mere presence of a join.

For a filter such as “orders with at least one qualifying item,” consider EXISTS rather than joining all qualifying items into the metric. If item attributes are needed, aggregate them to one row per order or define how the metric should split across attributes.

SUM(DISTINCT ...) is not a general repair. Distinct removes equal numeric values, not repeated business observations. Two legitimate orders can have the same weight or weighted contribution. A repair must establish the correct key, not discard coincidentally equal amounts.

Missing values must affect both sides consistently

Documented SQL behavior: SUM ignores null expressions, and an empty input produces a null sum in PostgreSQL. Aggregate functions.

That creates a subtle mismatch in SUM(rating * units) / SUM(units). An order with a null rating contributes nothing to the numerator but can still contribute its known units to the denominator. In West, including order 104 this way changes 8/2 into 8/6, about 1.3333. It effectively treats missing feedback like a zero contribution while retaining its weight.

Our eligible relation excludes the whole observation. That is a declared policy, not a universal rule. A different task may require imputation or a separate missing-feedback metric. Replacing missing weights with 1 is also a substantive change: it gives unknown observations a weight that the data did not provide.

Documented function behavior: NULLIF(denominator, 0) returns null when the denominator equals zero. It can protect a division, but it does not explain why the weight disappeared. Conditional expressions.

For an operational report, accompany the metric with counts of eligible orders, missing ratings, unknown or invalid weights, and zero weights. State whether those diagnostic categories may overlap. Otherwise, their sum can exceed the order count and look like another bug.

Correct totals require the underlying sums

The unweighted mean of East's 1.8 and West's 4.0 is 2.9. The combined unit-weighted rating is (18 + 8) / (10 + 2) = 2.1667, rounded to four decimals. East represents five times as much eligible weight as West.

Carry the two totals into the next grouping level. Using the same CTEs above, replace the final SELECT with:

SELECT SUM(numerator) / NULLIF(SUM(denominator), 0)
       AS overall_weighted_rating
FROM totals;

Do not reconstruct the numerator from an already rounded group average if the original numerator is available. Rounding creates information loss that compounds across many groups.

This pattern also explains an important distinction for rates. If each group's rate is successes divided by trials, weighting those rates by their trial counts reproduces total successes divided by total trials. Weighting by revenue or giving each group equal influence defines another metric.

The arithmetic identity assumes the groups partition the same eligible observations. If orders belong to overlapping regions or categories, summing their totals double-counts them. Correct formulas cannot fix an overlapping population model.

Numeric types and windows are part of the answer

Dialect facts: PostgreSQL integral division truncates toward zero. SQL Server's AVG return type depends on the input type, with integer inputs returning an integer type. Check the target engine rather than assuming every average becomes a decimal. PostgreSQL arithmetic; SQL Server AVG.

The exercise introduces 1.0 before summing quantities so its SQLite execution uses noninteger arithmetic. For production monetary or high-precision calculations, choose explicit decimal precision and scale suitable for the largest products and totals. Casting after an overflowing multiplication is too late. PostgreSQL documents the distinction between exact numeric and approximate floating types in its numeric-type reference.

A rolling weighted average also requires two rolling sums. If daily_totals(day, numerator, denominator) contains one row for every calendar day, this computes a three-day window:

SELECT day,
       SUM(numerator) OVER (
           ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) / NULLIF(SUM(denominator) OVER (
           ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 0) AS rolling_rating
FROM daily_totals
ORDER BY day;

Documented window behavior: aggregates used as window functions operate over the selected frame. Specify that frame deliberately. PostgreSQL window functions.

Here, three rows mean three days only because the relation has one row per day without gaps. With missing dates, the query means the last three recorded days. With multiple rows per day, it means the last three rows. Build the daily grain and calendar first when the requirement is calendar time.

Test invariants before trusting a plausible number

The example queries were executed locally in SQLite 3.51.0. This verifies the fixture and arithmetic; it does not certify every dialect's typing or execution plan.

Use these checks in an interview:

Split one item's quantity into two lines without changing its order total. The answer should stay the same.
Multiply every valid quantity by the same positive factor. The average should remain unchanged.
Add missing feedback or a zero-weight order. Confirm the declared eligibility policy.
Give two legitimate orders identical contributions. A distinct-based repair must not collapse them.
Compare the overall result with the numerator and denominator summed from groups.
With nonnegative weights and positive total weight, check that the result lies between eligible values' minimum and maximum.

The last condition is necessary, not sufficient: the wrong East result, 2.3333, still falls between 1 and 5. Explain a counterexample rather than relying on a range check alone.

Five related exercises to rehearse

Practice question	What to check next
Compute weighted response rates by job category	Rate weights
Reason About Composite Join Keys and Predicate Placement	Join grain
Calculate Monthly Conversion Rate and Average Order Value	Metric units
Compare WHERE vs HAVING with aggregates	Filters
Compute window averages and merge intervals	Frames

For each attempt, explain the observation, eligible population, and two totals before showing the division. Then introduce one join or missing-value change and predict its effect before running the query.