Solving Google Search Ranking Logic Using SQL (FAANG Interview Style)

Everybody uses Google.

When we perform a search, Google ranks the most relevant results at the top.

But have you ever been asked to replicate this ranking logic using SQL in an interview?

At first glance, this problem feels straightforward.
But in a real SQL interview, small details can make or break your answer.

In this post, we’ll walk through how to solve a Google-style search ranking problem using SQL, while also highlighting common interview pitfalls you must clarify with the interviewer.

---

🧩 Problem Statement

You are given a table called search_results with the following columns:

• query – search term entered by the user
• url – result URL
• score – relevance score
• is_active – whether the result is active (1 = active, 0 = inactive)
• created_at – when the result was created

Your goal:

For each search query, return the top K results, with these conditions:

1. Only include active results
2. Only include results from the last 30 days
3. Rank results by score (highest first)
4. Support a dynamic value of K (for example, top 2 results per query)

---

⚠️ Why This Question Is Tricky in Interviews

Many candidates jump straight into writing SQL.

But interviewers are testing whether you:

• Handle window functions correctly
• Understand ranking vs ties
• Write scalable and dynamic SQL
• Ask the right clarifying questions

We’ll cover all of that.

---

🛠 Step 1: Define a Dynamic Parameter (Top K)

In interviews, it’s always a good idea to show that your solution is flexible.

DECLARE @K INT = 2;

This allows you to easily change how many top results you want per query.

---

🛠 Step 2: Rank Results Per Query

We’ll use a window function to rank results within each query based on score.

ROW_NUMBER() OVER (
    PARTITION BY query
    ORDER BY score DESC) AS rn

• PARTITION BY query → ranking restarts for each search query

• ORDER BY score DESC → higher score = better ranking

---

🛠 Step 3: Apply Required Filters

We now apply the two filtering conditions:

1. Result must be active
2. Result must be from the last 30 days

WHERE is_active = 1
AND created_at >= DATEADD(DAY, -30, GETDATE())

This ensures:

• Inactive URLs are excluded
• Old or stale results are ignored

---

Full SQL Solution Using a CTE

Putting everything together:

WITH ranked_results AS (
    SELECT
        query,
        url,
        score,
        ROW_NUMBER() OVER (
            PARTITION BY query
            ORDER BY score DESC
        ) AS rn
    FROM search_results
    WHERE is_active = 1
      AND created_at >= DATEADD(DAY, -30, GETDATE())
)
SELECT
    query,
    url,
    score
FROM ranked_results
WHERE rn <= @K;

✅ This returns the top K results per search query,
✅ Filters inactive and old records,
✅ And uses a clean, interview-friendly structure.

---

🎯 Interview Follow-Up Question: How Do You Handle Ties?

This is where many candidates lose points.

You must clarify how ties should be handled.

Ranking Functions and Tie Behavior

Function	Handles Ties	Skips Numbers
ROW_NUMBER()	❌ No	❌ No
RANK()	✅ Yes	✅ Yes
DENSE_RANK()	✅ Yes	❌ No

Important Insight:

• ROW_NUMBER() randomly discards tied rows
• If ties should be included → use RANK() or DENSE_RANK()
• Interviewers often ask:

“Which function skips numbers for ties?”

Do you know the answer?
Drop it in the comments 😉

---

Key Takeaways for SQL Interviews

• Always clarify tie-handling rules
• Prefer CTEs for readability
• Use window functions for ranking problems
• Think beyond “getting the output” — think like an interviewer