How to calculate MAP@k in SQL

Why MAP matters

Your team just shipped a new ranker for the product search at a marketplace like Airbnb or DoorDash. On day one, the PM asks for a single number that captures "are we surfacing all the good results at the top, not just one?". CTR moves with novelty. NDCG needs graded labels you do not have. Mean Average Precision (MAP@k) is the answer most search and recsys teams reach for when relevance is binary and there can be more than one right answer per query.

MAP rewards rankers that pull every relevant item up the list, not just the first one. MRR only cares about where the first relevant document landed — fine for fact lookup, bad for "show me all the 2-bedroom listings in Lisbon under $200". MAP averages precision at every rank that contains a hit, then averages those numbers across queries. The result is a single scalar between 0 and 1 that punishes both missing relevant items and burying them below junk.

This guide builds the SQL from first principles, contrasts it with MRR and NDCG, and lists the five mistakes you will see in interviews at Stripe, Uber, or any team running a search system.

Average Precision in plain English

For a single query, Average Precision is computed against the ranked list returned by your model. Walk the list top to bottom. At each position where you find a relevant document, compute the precision up to that position. Sum those precisions. Divide by the total number of relevant documents (sometimes capped at k).

AP@k = (1 / total_relevant) * Σ over relevant ranks r ≤ k of precision@r

where precision@r = (relevant items in top r) / r

A worked example with k = 5 and the ranked list [1, 0, 1, 0, 1] (1 = relevant):

• Rank 1 hit: precision = 1/1 = 1.000
• Rank 3 hit: precision = 2/3 = 0.667
• Rank 5 hit: precision = 3/5 = 0.600
• Sum = 2.267, divide by total relevant = 3
• AP@5 = 0.756

Same list but flipped: [0, 0, 1, 1, 1] gives AP@5 = (1/3 + 2/4 + 3/5) / 3 = 0.478. Same number of hits, lower MAP — exactly because they were buried. That asymmetry is the whole point of the metric.

MAP@k in SQL

Assume a table search_results(query_id, rank, is_relevant, query_date) where each row is one document at one position for one query, and is_relevant is a boolean ground-truth label (clicked, purchased, judged by raters — your call). Compute MAP@10 over the last 30 days:

WITH ranked AS (
    SELECT
        query_id,
        rank,
        is_relevant,
        SUM(CASE WHEN is_relevant THEN 1 ELSE 0 END) OVER (
            PARTITION BY query_id ORDER BY rank
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS relevant_so_far
    FROM search_results
    WHERE query_date >= CURRENT_DATE - INTERVAL '30 days'
      AND rank <= 10
),
precision_at_relevant AS (
    SELECT
        query_id,
        rank,
        relevant_so_far::NUMERIC / rank AS precision_at_rank
    FROM ranked
    WHERE is_relevant = TRUE
),
total_relevant_per_query AS (
    SELECT
        query_id,
        COUNT(*) AS total_relevant
    FROM search_results
    WHERE query_date >= CURRENT_DATE - INTERVAL '30 days'
      AND is_relevant = TRUE
    GROUP BY query_id
),
ap_per_query AS (
    SELECT
        p.query_id,
        SUM(p.precision_at_rank) / NULLIF(t.total_relevant, 0) AS ap
    FROM precision_at_relevant p
    JOIN total_relevant_per_query t USING (query_id)
    GROUP BY p.query_id, t.total_relevant
),
all_queries AS (
    SELECT DISTINCT query_id
    FROM search_results
    WHERE query_date >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
    AVG(COALESCE(ap, 0))         AS map_at_10,
    COUNT(*)                     AS total_queries,
    SUM(CASE WHEN ap IS NULL THEN 1 ELSE 0 END) AS queries_with_no_relevant
FROM all_queries
LEFT JOIN ap_per_query USING (query_id);

Three things to notice. First, the ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame is explicit on purpose — some engines default to RANGE which behaves differently with ties. Second, queries that returned zero relevant documents get AP = 0 rather than being dropped; otherwise MAP becomes a metric of "queries where I already won". Third, total_relevant comes from the whole judged set, not just top-k — see pitfall 4 below for the variant.

MAP vs MRR

MRR (Mean Reciprocal Rank) is 1 / rank_of_first_relevant, averaged over queries. It is always ≥ MAP because MAP includes all hits, and the precisions at later hits are always ≤ the precision at the first hit. A compact comparison:

WITH ranked AS (
    SELECT
        query_id, rank, is_relevant,
        SUM(CASE WHEN is_relevant THEN 1 ELSE 0 END) OVER (
            PARTITION BY query_id ORDER BY rank
        ) AS relevant_so_far
    FROM search_results
    WHERE rank <= 10
),
ap_per_query AS (
    SELECT
        query_id,
        SUM(relevant_so_far::NUMERIC / rank) FILTER (WHERE is_relevant)
        / NULLIF(SUM(CASE WHEN is_relevant THEN 1 ELSE 0 END), 0) AS ap
    FROM ranked
    GROUP BY query_id
),
first_relevant AS (
    SELECT query_id, MIN(rank) AS first_rank
    FROM ranked
    WHERE is_relevant
    GROUP BY query_id
)
SELECT
    AVG(1.0 / fr.first_rank)        AS mrr,
    AVG(COALESCE(ap.ap, 0))         AS map
FROM first_relevant fr
LEFT JOIN ap_per_query ap USING (query_id);

When your queries have exactly one relevant document each (think known-item search: "find the Wikipedia page for Stripe"), MAP collapses to MRR mathematically — same number, simpler SQL.

MAP vs NDCG

For binary judgements where you only know "relevant / not", MAP is the cleaner choice. The moment your raters start labelling bad / okay / great / perfect, switch to NDCG — collapsing graded labels to binary throws away the signal you paid raters for.

Common pitfalls

When teams compute MAP for the first time, the most common mistake is to drop queries that returned zero relevant documents from the denominator. This silently turns MAP into a conditional metric — average precision given that we already won this query. The fix is to keep those queries with AP = 0, which is exactly what the LEFT JOIN ... COALESCE(ap, 0) pattern above does. Skip this and your MAP will look great while your search is quietly broken on a third of head queries.

A second trap is computing precision against the wrong denominator. Precision at rank r is relevant_so_far / r, where r is the position in the result list — not the total number of documents in your index. The window-function pattern is hard to get wrong, but rolling your own with self-joins frequently produces "precision = relevant / 1_000_000", which makes every model look identical and useless. If your MAP@10 is below 0.001, this is almost always why.

A third pitfall is single-relevant queries. If every query in your judged set has exactly one relevant document, MAP@k equals MRR@k by construction. You burned engineering time on a more complicated metric for zero new information. The fix is to inspect the distribution of total_relevant per query before picking the metric — if the median is 1, ship MRR and move on.

The fourth issue is the "unbounded total relevant" question. Strict MAP divides by total_relevant over the full judgement set, including relevant documents that fell below rank k. AP@k variants instead divide by min(k, total_relevant). Both are defensible, but mixing them across leaderboards or experiments makes results incomparable. Pick one, write it on the metric definition page, and never change it without a model freeze.

The fifth pitfall is reporting MAP without a confidence interval. Per-query AP has high variance — head queries with many relevant items dominate, tail queries with one item bounce between 0 and 1 between model retrains. Always bootstrap at least 1,000 query resamples to get a CI before claiming a model is better. A 0.4% MAP gain with a ±2% CI is noise, not a launch.

Optimization tips

For tables in the millions of rows range, the window-function MAP query is fine on Postgres or Snowflake — single pass, no self-joins, scales linearly. The cost driver is the PARTITION BY query_id ORDER BY rank sort, which benefits from a composite index (query_id, rank) on Postgres or clustering keys (query_id, rank) on Snowflake and BigQuery. On ClickHouse, an ORDER BY (query_id, rank) MergeTree table will let the engine skip the sort entirely.

For evaluation runs in CI that recompute MAP on every model push, materialise ap_per_query as a daily table. Pre-aggregating to per-query AP collapses billions of (query, document) rows down to one row per query, which makes the dashboard query a sub-second AVG(). This is also what lets you slice MAP by query segment — head/torso/tail, locale, device — without re-walking the raw judgement table.

If you are comparing two rankers side-by-side, compute paired AP differences per query instead of two separate averages. The variance of AP_A - AP_B is much smaller than the variance of either side because most of the noise is query-specific and cancels out. The bootstrap CI on the paired delta is what the launch committee actually wants to see.

Related reading

• SQL window functions interview questions
• How to calculate Mean Reciprocal Rank in SQL
• How to calculate Hit Rate@k in SQL
• How to calculate Bootstrap CI in SQL

If you want to drill ranking-metric SQL questions like this every day, NAILDD is launching with 500+ SQL problems across exactly this pattern.

FAQ

What is the difference between MAP and MAP@k?

MAP evaluates the full ranked list returned by the system, while MAP@k truncates at rank k before computing precision. In production, k is usually the size of the first results page (10 for web search, 25-50 for e-commerce, 3-5 for mobile). Reporting both MAP and MAP@k can be useful — MAP@k tracks user-visible quality, while MAP at full depth tracks recall-style behaviour of the underlying retrieval stack.

What is a "good" MAP score?

It depends on the corpus and the judgements. On classic TREC web search benchmarks, 0.4-0.6 is strong and anything above 0.7 is suspicious. In recsys on consumer apps, 0.1-0.3 is normal because the relevant set per user is small and noisy. The only honest answer is to compute MAP for your current production model and use that as the floor — anything below it is a regression, regardless of the absolute number.

Should I use MAP for binary or graded relevance?

Use MAP when your judgements are binary (relevant vs not). For graded relevance — for example a 0/1/2/3 scale of "bad/okay/great/perfect" — switch to NDCG, which uses the grade in its numerator. Collapsing graded labels to binary to fit MAP throws away the signal you paid annotators to produce, and it tends to over-reward rankers that confidently surface "okay" results.

Is AP computed per query or per system?

AP (Average Precision) is a per-query number. MAP (Mean Average Precision) is the mean of those per-query APs across the whole evaluation set. Always report MAP with the number of queries in the denominator so reviewers can spot accidental filtering — if you say "MAP@10 = 0.42 over 800 queries" and the judged set has 1,200, someone needs to explain the missing 400.

Can I use MAP for a personalised feed instead of search?

Yes, with one caveat. MAP requires a ground-truth set of "relevant items" per user, which is easy when you have explicit signals (saves, purchases, swipe-rights) and harder for pure consumption feeds where everything shown gets some attention. For a feed, you typically define relevance as "engaged within N seconds" or "session-completed", then compute MAP@k where k is the number of items in the visible viewport. Document the choice — different definitions of relevance produce non-comparable MAP numbers.

How does MAP behave when k is larger than the number of relevant documents?

It does not break, but the metric becomes easier to satisfy. With three relevant documents and k = 100, AP can hit 1.0 if all three appear at ranks 1-2-3, regardless of what fills ranks 4-100. The fix, if this matters for your use case, is to cap k at a value that reflects actual user viewport depth — almost no one scrolls past rank 20 on mobile, so MAP@1000 is just a vanity number.