Multi-factor weighted sorting for relevance

The problem

You want to sort items by “relevance” and there is no single number that captures it. A product listing has rating, recency, popularity, price, and a freshness flag. A search result has text-match score, click-through history, and recency. A feed has all of the above plus social proof.

The naive shapes fail in predictable ways:

• Sort by one column. Whichever signal you pick, the others vanish. New listings never surface; popular old listings dominate forever.
• ORDER BY rating DESC, sales DESC, …. Lexicographic priority — the second column only breaks ties on the first. With float ratings, ties are rare, so the rest of the columns may as well not exist.
• Sum the raw signals. rating + sales + views looks reasonable until you realise sales is 0–1,000,000 and rating is 0–5. Sales swamps everything.

The pattern that works: normalise each signal to a common scale, weight them, sum. The interesting decisions are which signals, how to normalise, and how to set the weights.

The shape

The maths

For each item i and signal s, compute the normalised value nᵢ,s ∈ [0, 1], then:

score(i) = Σ ws · nᵢ,s     where Σ ws = 1

Keeping the weights summing to 1 isn’t strictly required, but it makes the final score itself a [0, 1] number, which is easy to reason about and easy to threshold.

Normalisation per signal type

The bug lurks here. The right normalisation depends on the signal.

The log-scale step matters a lot for power-law distributions like view counts. Linear normalisation of “0 to 1M views” puts everything from 0 to ~200K into the bottom 20% of the range and crushes the signal. Log-scaling makes the gap between 10 and 100 views worth the same as the gap between 10K and 100K — usually what you actually want.

Worked example

A product listing with four signals. Imagine three items, normalised values shown:

item     rating  views  recency  featured
A        1.0     0.40   0.20     0
B        0.6     1.0    0.95     1
C        0.8     0.60   0.50     0

Pick weights (0.4, 0.3, 0.2, 0.1) — rating matters most, views next, recency next, featured boost smallest:

A = 0.4·1.0 + 0.3·0.40 + 0.2·0.20 + 0.1·0 = 0.56
B = 0.4·0.6 + 0.3·1.0  + 0.2·0.95 + 0.1·1 = 0.83
C = 0.4·0.8 + 0.3·0.60 + 0.2·0.50 + 0.1·0 = 0.60

Ranking: B, C, A. A wins on rating but loses everywhere else; B is the well-rounded one. Now change the recency weight to 0.6 (cut from rating) and re-run — that’s the entire tuning loop.

In SQL

The whole pattern fits in one query. Compute maxs in a subquery (or with window functions) so the normalisation references the result-set extremes, not constants you’d have to maintain.

WITH bounds AS (
  SELECT
    MAX(view_count) AS max_views,
    MAX(age_days)   AS max_age
  FROM listing
  WHERE category = 'electronics'
)
SELECT
  l.*,
    0.4 * ((rating - 1.0) / 4.0)
  + 0.3 * (LN(1 + view_count) / LN(1 + b.max_views))
  + 0.2 * EXP(-age_days / 30.0)
  + 0.1 * (CASE WHEN featured THEN 1 ELSE 0 END) AS score
FROM listing l
CROSS JOIN bounds b
WHERE l.category = 'electronics'
ORDER BY score DESC
LIMIT 50;

A few things worth noting in that query:

• (rating - 1.0) / 4.0 maps 1..5 to 0..1. The bounds are known for this column (it’s a 1–5 star rating), so we hard-code them rather than computing per query.
• LN(1 + …) over LN(1 + max) is the log-min-max for the unbounded count.
• EXP(-age_days / 30.0) is a 30-day half-life-ish decay. At 30 days the recency signal is e⁻¹ ≈ 0.37; at 90 days it’s e⁻³ ≈ 0.05. The denominator is the tuning knob.
• The featured boost is intentionally small — a featured flag that adds 0.1 cannot rescue an item that scores 0.2 on everything else. That’s the point.

Tuning the weights

The honest answer is: by hand, with a holdout set of pairs the team agrees on the right order for. A handful of patterns:

1. Start with a guess and a single example

   Pick weights that match your intuition (e.g. rating > views > recency > flags). Compute scores for ~10 items you have strong opinions about. Adjust until the ranking matches your intuition.

2. Build a labelled pair set

   Collect 50–200 pairs (A, B, correct_order) from product, support, or user research. The set is the ground truth.

3. Grid-search weights

   Try a coarse grid (step = 0.1) over weights that sum to 1. Pick the combination that maximises ”% of pairs ranked correctly”. Refine around the winner.

4. Re-test on incidents

   Whenever someone files a “why is X ranked here?” bug, add the example to the pair set. Re-run grid search. The set is the note the system learns from.

This is what learning-to-rank ML systems automate. For most teams, manual tuning against a small labelled set is more than enough, ships in a day, and stays explainable forever.

The footguns

Other ways this goes wrong:

• Normalising against the entire table, not the result set. One global outlier compresses every item’s score for every query. Normalise inside the query, against the current rows.
• Letting one signal saturate. If views ranges from 1 to 50M, even after log-scaling a small number of items occupy the top 90% of the normalised range. Cap or winsorise before scaling.
• Mixing scales of “higher is better” and “lower is better” without inverting. Forgetting 1 - normalised(price) puts the most expensive item at the top.
• Tuning weights against vibes. Without a labelled pair set, “this feels right” drifts on every meeting. The set is what keeps the conversation grounded.

What to remember