Calculating Mean Reciprocal Rank in SQL

Why MRR matters

Your search PM pings you on Slack at 9:47 on a Tuesday morning: "We shipped the new query rewriter on Friday — is it actually better?" You open the eval table, but the only thing the team agreed on last quarter is one number — Mean Reciprocal Rank. You need a defensible SQL query that returns a single value, plus a hit-rate, plus a per-model breakdown, before the 10 AM standup.

Mean Reciprocal Rank (MRR) is the canonical metric for search, question-answering, and any ranking task where the position of the first relevant result is what the user actually feels. If your top hit is always right, MRR = 1.0. If your system never surfaces a relevant document, MRR = 0. The metric is brutally simple — and that's exactly why it's the first thing you ship to production dashboards before you reach for NDCG or MAP.

A quick disambiguation before we touch SQL. In SaaS finance, "MRR" means Monthly Recurring Revenue. In information retrieval, it means Mean Reciprocal Rank. Same three letters, completely different formulas. Context resolves it, but if you're presenting to a mixed audience of finance and search folks, write the full term once before you use the acronym.

The formula

The mathematical definition fits on one line:

MRR = (1/N) * Σ (1 / rank_first_relevant_i)

Where N is the number of queries in your evaluation set and rank_first_relevant_i is the position (1-indexed) of the first relevant document for query i. If query i has no relevant document at all, the reciprocal rank is 0, not undefined.

Notice how aggressively the reciprocal punishes positions 2-3 versus position 1. That sharpness is the feature, not a bug — MRR is built for tasks where the user wants one right answer immediately, not a balanced page of options.

MRR in SQL

Assume a table search_results with one row per (query_id, doc_id) pair, a 1-indexed rank column, and a boolean is_relevant flag from human raters or click logs. The denominator must include every query in your evaluation window — including queries that have zero relevant documents anywhere in the candidate set.

WITH all_queries AS (
    SELECT DISTINCT query_id
    FROM search_results
    WHERE query_date >= CURRENT_DATE - INTERVAL '30 days'
),
first_relevant AS (
    SELECT
        query_id,
        MIN(rank) AS first_relevant_rank
    FROM search_results
    WHERE is_relevant = TRUE
      AND query_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY query_id
)
SELECT
    AVG(COALESCE(1.0 / fr.first_relevant_rank, 0)) AS mrr,
    COUNT(fr.query_id) AS queries_with_hit,
    COUNT(aq.query_id) AS total_queries,
    COUNT(fr.query_id)::NUMERIC * 100
        / NULLIF(COUNT(aq.query_id), 0) AS hit_rate_pct
FROM all_queries aq
LEFT JOIN first_relevant fr USING (query_id);

The LEFT JOIN is the entire point — it preserves the queries that produced no relevant hit so they show up in the average with a reciprocal-rank of zero. The COALESCE then turns the SQL NULL into a numeric 0 for the averaging step. Without both moves, your dashboard quietly reports a flattering number that won't match what a Python evaluation script produces.

The bonus columns — queries_with_hit and hit_rate_pct — are the two numbers you actually want next to MRR in every dashboard. A model with MRR = 0.45 and hit-rate = 62% is doing something very different from a model with MRR = 0.45 and hit-rate = 95%, even though the headline number is identical.

MRR@k for realistic scrolling

In production search, nobody scrolls to position 50. The metric you actually ship is MRR@k, where k is typically 5 or 10 — matching how many results fit on screen before the user gives up or reformulates the query.

WITH all_queries AS (
    SELECT DISTINCT query_id
    FROM search_results
    WHERE query_date >= CURRENT_DATE - INTERVAL '30 days'
),
first_relevant_in_top_k AS (
    SELECT
        query_id,
        MIN(rank) AS first_relevant_rank
    FROM search_results
    WHERE is_relevant = TRUE
      AND rank <= 10
      AND query_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY query_id
)
SELECT
    AVG(COALESCE(1.0 / first_relevant_rank, 0)) AS mrr_at_10
FROM all_queries aq
LEFT JOIN first_relevant_in_top_k fr USING (query_id);

The only delta from the full-set query is AND rank <= 10 inside the filtered CTE. Queries whose first relevant document sits at position 11 or worse contribute zero. This is how the metric mirrors actual user behavior — a result that's technically "in the index" but invisible on the first page does no useful work for the searcher.

Comparing models with MRR

When you're evaluating a query-rewriter or a re-ranker, you almost never look at MRR in isolation — you look at the per-model delta on a fixed evaluation set.

SELECT
    model_name,
    AVG(COALESCE(1.0 / first_relevant_rank, 0)) AS mrr_at_10,
    COUNT(*) AS queries_evaluated
FROM model_evaluations
WHERE eval_date >= CURRENT_DATE - INTERVAL '7 days'
  AND first_relevant_rank <= 10 OR first_relevant_rank IS NULL
GROUP BY model_name
ORDER BY mrr_at_10 DESC;

As a rule of thumb, an MRR delta of ≥ 0.02 on 1,000+ queries is usually distinguishable from noise. Anything smaller deserves a paired bootstrap before you cite it in a planning doc. On smaller eval sets — say, 200 queries — even a 0.05 absolute delta can flip from sign to sign across resamples.

Common pitfalls

When teams first wire up MRR in SQL, the most common mistake is to filter out queries with no relevant results before averaging. The intuition is reasonable — "why average over queries the system couldn't possibly answer?" — but it inflates MRR by silently shrinking the denominator. The fix is the LEFT JOIN pattern from the section above: every query in the eval window counts, even if it contributes a clean zero.

A second trap is reaching for MAX(rank) instead of MIN(rank) when joining to the relevance table. MRR is defined on the position of the first relevant document. Using the last relevant document instead gives you something that's not a standard metric at all — it's neither MAP nor MRR, just a number that goes down whenever your system is good. This bug hides well because it produces plausible-looking values in the 0.1-0.3 range.

A third pitfall is reporting raw MRR without a k cutoff. In offline evaluation it's tempting to count any relevant document at any position, because more "successes" sounds better. But a document at position 47 is invisible to users on every surface that matters, and including it makes your metric drift away from anything you can ship to a product dashboard. Always pair MRR with an explicit cutoff — MRR@5 or MRR@10 — and never compare numbers across different cutoffs.

Fourth: confusing single-relevance with multi-relevance evaluation. MRR by construction only looks at the first relevant document and ignores the rest. If your task has multiple correct answers per query — image search, related-products carousels, knowledge-graph entities — MRR systematically underweights models that surface diverse correct results. Reach for MAP or NDCG instead, and keep MRR as the secondary metric for "did we get at least one right thing on top."

Fifth, and the one that bites senior people: reporting MRR without a confidence interval. The standard fix is a bootstrap over query IDs — resample with replacement 1,000 times, recompute MRR each time, and report the 2.5th and 97.5th percentiles as your 95% CI. A reported MRR of 0.43 [0.41, 0.45] tells a different story than 0.43 [0.32, 0.54], and the 0.01 gap between two model variants is meaningless if the CIs overlap by half their width.

Optimization tips

The search_results table grows fast — every served query times the candidate set size. For an eval window over hundreds of millions of rows, partition the table by query_date so the 30-day filter prunes partitions before any aggregation runs. On Snowflake, the equivalent is a clustering key on (query_date, query_id); on BigQuery, use partitioned tables with a clustering column on query_id.

If you're computing per-model MRR daily for a dashboard, materialize the first_relevant_rank CTE as a daily table rather than recomputing it from raw eval logs every refresh. The MIN(rank) ... WHERE is_relevant aggregation is the expensive part — caching the per-query, per-model minimum reduces a dashboard query that takes 40 seconds on raw logs to one that returns in under a second from the pre-aggregated layer.

For confidence intervals, do the bootstrap outside SQL — pull the per-query reciprocal ranks into a Python notebook and resample there. Most warehouses don't have a native efficient bootstrap, and trying to fake it with GENERATE_SERIES + correlated subqueries usually melts the query optimizer.

Related reading

• How to calculate hit rate at k in SQL
• Feed ranking system design (DS interview)
• SQL window functions interview questions
• A/B testing peeking mistake

If you want to drill SQL questions like this every day, NAILDD is launching with 500+ SQL problems built around exactly this pattern — ranking metrics, cohort analysis, and the joins that actually show up in interviews.

FAQ

What's the difference between MRR and NDCG?

MRR only cares about the position of the first relevant document — everything after it is ignored. NDCG (Normalized Discounted Cumulative Gain) considers every position in the ranking, weights graded relevance scores, and discounts results logarithmically the further down the page they sit. NDCG is richer and the right call when relevance is multi-valued (highly relevant vs. somewhat relevant vs. off-topic). MRR is simpler, easier to communicate to non-IR stakeholders, and the standard for tasks like question-answering or navigational search where there's essentially one right answer per query.

What MRR score is considered good?

It's heavily domain-dependent and you should never benchmark across domains. For mature web search, state-of-the-art systems land in the 0.6 to 0.8 range on standard benchmarks like MS MARCO. For open-domain question answering, 0.4 to 0.6 is competitive. For recommender systems with personalized feeds, 0.3 to 0.5 is normal, and below 0.2 generally signals either a cold-start problem or that you're using MRR on a task where a multi-relevance metric like MAP would be more appropriate.

How does MRR handle multiple relevant documents per query?

It only looks at the first one. If query q has relevant documents at positions 1, 4, and 7, the reciprocal rank is 1/1 = 1.0 — the system gets full credit and the documents at positions 4 and 7 are invisible to the metric. This is the right behavior for question-answering, but it understates models that surface diverse correct results. For multi-answer tasks, use MAP (Mean Average Precision) instead, or report MRR alongside recall@k.

Does MRR work with binary relevance only?

Yes, and that's its native habitat. MRR assumes a binary is_relevant flag — a document either counts or it doesn't, with no graded scoring. If your raters produce graded relevance scores (e.g., 0/1/2/3), you have two options: threshold them into binary for MRR (typically grade ≥ 2 = relevant) or switch to NDCG, which natively consumes graded labels.

Can I use MRR for recommender feeds?

Yes, especially for surfaces where one recommendation dominates the user's attention — the top result on a homepage carousel, the first suggested product in a checkout flow, the first autocomplete suggestion. For long feeds where the user scrolls deeply, MRR underweights the long tail and you should pair it with hit-rate@k and a coverage metric. Treat MRR as your "top-of-funnel quality" signal and reach for NDCG or MAP when you need to evaluate the full feed.

How do I compute confidence intervals around MRR?

Bootstrap over query IDs, not over rows. Sample N query IDs with replacement (where N is your eval set size), recompute MRR on each resample, and repeat 1,000 times. The 2.5th and 97.5th percentiles of the resulting distribution give you a 95% confidence interval. Resampling over rows instead of queries underestimates variance because it breaks the within-query correlation between rank positions.