How to calculate Hit Rate at k in SQL

What is Hit Rate@k and why it matters

Hit Rate@k is the share of queries for which at least one relevant document appears in the top-k results returned by a search or recommendation system. It is the simplest recall-style metric in information retrieval: position inside the top-k does not matter, only presence. If your HitRate@10 is 80%, then for 8 out of every 10 queries the user sees a correct answer somewhere in the first 10 results. Whether that answer sits at rank 1 or rank 9 is invisible to this metric.

Picture a Monday at a company like Airbnb, DoorDash, or Snowflake. A product manager pings the search team in Slack: "the new ranker shipped on Friday, how is it doing?" You need a single number an executive can read in three seconds. Latency and click-through rate alone do not tell you whether the system is finding the right results, only whether it is fast and getting clicked. Hit Rate@k closes that gap because it ties directly to the user-visible window: the first ten listings, the first five product cards, the top three suggested videos. Engineers love NDCG because it captures ranking quality, but stakeholders rarely build intuition for a 0.42 NDCG number. They do build intuition for "78% of searches surface at least one good match in the top 10".

The metric also serves as a canary during retrieval-model rollouts. When you swap a BM25 baseline for a dense vector retriever, or move from a single embedding model to a hybrid retriever in a Databricks RAG pipeline, Hit Rate at a handful of k values is usually the first chart on the dashboard. Drops here mean candidate generation is broken, regardless of what the final ranker does downstream.

The SQL formula

The math is intentionally boring:

HitRate@k = (1 / N) * Sum over queries of I(at least one relevant doc in top-k)

I is an indicator that takes the value 1 when the condition holds and 0 otherwise, and N is the total number of queries. The range is [0, 1]. To translate this into SQL, you need a search_results table with one row per (query, returned document, rank) tuple, plus a boolean is_relevant flag from your labelers or implicit-feedback pipeline.

WITH query_hits AS (
    SELECT
        query_id,
        MAX(CASE WHEN is_relevant = TRUE AND rank <= 10 THEN 1 ELSE 0 END) AS has_hit_at_10
    FROM search_results
    WHERE query_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY query_id
)
SELECT
    SUM(has_hit_at_10)::NUMERIC * 100 / NULLIF(COUNT(*), 0) AS hit_rate_at_10_pct,
    COUNT(*) AS total_queries
FROM query_hits;

The MAX(CASE WHEN ...) pattern is the workhorse: per query, it collapses all ranked rows into a single 0/1 flag answering "did we hit?". The outer aggregate then averages that flag across queries. Two small details prevent footguns. First, NULLIF(COUNT(*), 0) keeps the query from dividing by zero on empty days. Second, the ::NUMERIC cast prevents integer-division truncation in Postgres-like dialects.

For Snowflake or BigQuery, swap INTERVAL '30 days' for the dialect's equivalent (DATEADD('day', -30, CURRENT_DATE()) or DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)). The shape of the query is the same.

Computing Hit Rate at multiple k

In practice you almost never want Hit Rate at a single k. Stakeholders ask for @1, @5, @10, and @20 in the same chart because the curve tells a story the single point cannot.

WITH query_results AS (
    SELECT
        query_id,
        rank,
        is_relevant
    FROM search_results
    WHERE query_date >= CURRENT_DATE - INTERVAL '30 days'
),
hits AS (
    SELECT
        query_id,
        MAX(CASE WHEN is_relevant AND rank <= 1  THEN 1 ELSE 0 END) AS hit_1,
        MAX(CASE WHEN is_relevant AND rank <= 5  THEN 1 ELSE 0 END) AS hit_5,
        MAX(CASE WHEN is_relevant AND rank <= 10 THEN 1 ELSE 0 END) AS hit_10,
        MAX(CASE WHEN is_relevant AND rank <= 20 THEN 1 ELSE 0 END) AS hit_20
    FROM query_results
    GROUP BY query_id
)
SELECT
    AVG(hit_1)  * 100 AS hr_at_1_pct,
    AVG(hit_5)  * 100 AS hr_at_5_pct,
    AVG(hit_10) * 100 AS hr_at_10_pct,
    AVG(hit_20) * 100 AS hr_at_20_pct
FROM hits;

Hit Rate@k is monotonically non-decreasing in k: a larger window contains more documents, never fewer. The four numbers above always form an ascending sequence. The shape of that sequence is what matters. Suppose a team at Stripe or Notion ships a new retriever and sees HR@1 = 22%, HR@5 = 61%, HR@10 = 82%, HR@20 = 91%. The jump from @1 to @5 is huge, then the curve flattens. Candidate generation is healthy (relevant docs are somewhere in the top 20) but the final ranker is weak. Compare to a baseline at HR@1 = 35%, HR@5 = 70%, HR@10 = 78%, HR@20 = 80%: the curve flattens earlier — the ranker is sharper, but recall is bounded.

A useful heuristic: if the gap between HR@5 and HR@10 is more than 15 points, your ranker is losing on the diving-board test. Investigate features that distinguish near-misses from clear winners.

Hit Rate by query segment

A single global Hit Rate hides the parts of the product that are quietly broken. The same retriever can score 90% on head queries and 35% on long-tail queries, and the global average makes that look fine.

WITH per_query AS (
    SELECT
        query_category,
        query_id,
        MAX(CASE WHEN is_relevant AND rank <= 10 THEN 1 ELSE 0 END) AS has_hit
    FROM search_results
    WHERE query_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY query_category, query_id
)
SELECT
    query_category,
    AVG(has_hit) * 100 AS hr_at_10_pct,
    COUNT(*) AS queries
FROM per_query
GROUP BY query_category
HAVING COUNT(*) >= 50
ORDER BY hr_at_10_pct ASC;

Sort ascending so the weakest segments float to the top — that is your bug list. The HAVING COUNT(*) >= 50 filter avoids over-reacting to small categories where a single bad day produces dramatic swings. Slice by any dimension you have: query language, query length, device, logged-in vs anonymous. At Uber or DoorDash, splitting by city tier usually reveals the global metric is dragged down by locales with sparse training data.

Common pitfalls

The most common mistake is conflating Hit Rate with precision. Hit Rate@10 answers "is there at least one relevant document in the top 10?", which is a recall-flavored question. Precision@10 answers "what fraction of the top 10 are relevant?". You can have HR@10 = 100% and Precision@10 = 10% — every query has exactly one relevant doc, and it always lands somewhere in the top 10. The fix is not to choose one metric and abandon the other; report both, and pair them with NDCG or MAP when ranking quality matters.

A subtler trap is multiple-relevant queries. If every query in your eval set has 30 relevant documents, Hit Rate@10 will sit near 100% for any reasonable retriever and provide zero signal. This is exactly the case for product search on Amazon-scale catalogs, where "running shoes" might have hundreds of valid matches. In that regime, you want Recall@k (the fraction of all relevant docs you recovered) or NDCG (which rewards getting the best ones to the top). Hit Rate is most useful when each query has roughly one to three correct answers — RAG question-answering, deduplication, near-duplicate detection.

Ignoring sample size is another classic. A Hit Rate computed on 100 queries has a confidence interval roughly 10 percentage points wide. You will see day-to-day swings that look like model regressions but are pure noise. As a rule of thumb, you want at least 1,000 queries per slice you plan to act on, and 10,000+ for headline reporting. If you do not have that volume, bootstrap a 95% CI alongside the point estimate and put both on the dashboard.

Treating clicks as ground-truth relevance feels convenient but corrupts the metric. A user might click a result, take one look, and bounce immediately — that is a click, not a hit. If your is_relevant column is sourced from raw clicks, your Hit Rate is really a Click-Through Rate in disguise, with all the position bias that implies. Better signals: clicks with dwell time above 30 seconds, explicit thumbs-up feedback, or a separate human-labeled eval set refreshed quarterly.

Finally, treat the choice of k as part of the experiment. Model A might beat Model B at HR@10 but lose at HR@5, and the right answer depends on the product. A mobile app showing five results per screen cares about HR@5; a desktop dashboard showing twenty cares about HR@20. Always report at the k that matches the user surface, plus one neighbor on each side, so reviewers can see how the comparison shifts.

Optimization tips

When search_results grows past tens of millions of rows, the naive query starts to feel slow. The most effective change is partitioning on query_date (daily or weekly) and pruning at the WHERE clause. Snowflake and BigQuery do this automatically when you cluster correctly; on Postgres, declarative partitioning with monthly child tables is enough.

For hourly-refreshed dashboards, materialize a per-query aggregate. Store one row per (query_id, query_date, k) with a precomputed has_hit flag. The dashboard query becomes a simple AVG() over a much smaller table. This pattern works especially well when comparing many k values: compute once, slice cheaply at read time.

An indexing trick for row stores: a composite index on (query_date, query_id, rank) lets the planner satisfy the date filter and the per-query grouping without a full sort. Adding is_relevant as an INCLUDE column makes the query index-only on Postgres 11+.

When comparing two retrievers in an A/B test, do not compute two independent Hit Rates and subtract. Compute the paired difference at the query level — for queries served by both arms, was it a hit under A, under B, or both? The paired variance is much smaller than two unpaired means, so you reach significance with half the traffic.

Related reading

• SQL window functions interview questions
• How to calculate F1 score in SQL
• How to calculate confusion matrix in SQL
• How to calculate AUC-ROC in SQL
• How to calculate cosine similarity in SQL

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

FAQ

Hit Rate vs Recall — are they the same?

They overlap but are not identical. Recall@k is the fraction of all relevant documents that appear in the top-k results: if a query has 5 relevant docs and 3 show up in the top 10, Recall@10 is 0.6. Hit Rate@k is binary: did at least one relevant doc appear? In the special case where every query has exactly one relevant document, the two metrics are mathematically equivalent. For most RAG and question-answering systems that one-relevant-per-query regime is realistic, which is why papers in that area often quote Hit Rate.

Which k should I use?

The k that matches your user surface. For interfaces that show a single answer — voice assistants, "I'm feeling lucky" style suggestions, top-of-page direct answers — use HR@1. For typical search result pages, @5 and @10 are the standard. For infinite-scroll feeds or recommendation rails that let users browse, @20 or even @50 is more honest. A common practice is to report @1, @5, @10 together; the curve across those three values is more informative than any single point.

How do I interpret Hit Rate on long-tail queries?

Long-tail queries — rare strings that the system has never seen before — almost always score lower, often below 50%. This is normal and expected. The retriever has less training signal for rare queries, and human relevance labels for them are sparse and noisy. The right move is to track long-tail Hit Rate as its own segment with its own target, not to expect it to match head-query numbers. A reasonable goal is to close the gap to head queries by 5-10 percentage points per quarter through better embedding models, query rewriting, or fallback strategies.

Can I compare Hit Rate numbers across different datasets?

Carefully. Hit Rate depends heavily on prevalence (how many relevant documents exist per query in your corpus) and on query difficulty. A 75% HR@10 on a curated benchmark like MS MARCO does not mean the same retriever will hit 75% on your production traffic. Use Hit Rate to compare two models on the same dataset, or to track one model over time. Cross-dataset comparisons need additional context — the average number of relevant docs per query, the query distribution, the labeling protocol.

Is Hit Rate useful in production without ground-truth labels?

Not directly — you need someone or something to mark is_relevant. Teams usually approximate Hit Rate with click-through rate or a "successful session" proxy: did the user click any result, stop searching for the same query, convert. These proxies have well-known biases (position, presentation) but correlate with the offline metric well enough to use as guardrails. A common cadence: weekly CTR monitoring, quarterly human-labeled Hit Rate eval, annual recalibration of the proxy against gold-standard labels.

How does Hit Rate@k behave during a cold start?

Poorly, and predictably. A new retrieval system has thin coverage of the long tail, and HR@k will look worse than offline numbers suggested. Offline eval sets are usually built from logged head traffic, so they underrepresent the queries that hurt cold-start performance. Plan for HR@10 to dip by 10-20 points in the first two weeks and recover as the system warms up. For an honest pre-launch estimate, weight your offline eval by the production query distribution rather than treating every query equally.