Query Quality Analysis

These calculations assume positive, non-NULL position values. AVG(rating * 1.0 / position) is an equivalent direct expression where decimal coercion is needed.

Problem Statement

A search engine logs data about every search query it runs: what results it showed, where each result was ranked, and how users rated those results. Analysts want to measure how good each query’s overall performance is.

Business context A search engine’s whole job is to show the best results at the top. If a badly-rated result shows up at position 1, that’s a much bigger problem than a badly-rated result buried at position 200. This problem builds a single score that captures both “how good was the result” and “how prominently was it shown,” combined into one number per query.

Goal For each unique search query, calculate two things: a “quality” score, and a “poor query percentage” (how often the results were rated poorly).

Input A table logging every individual search result shown for every query, along with its position and its user rating.

Output One row per query, showing the query name, its quality score, and its poor query percentage, both rounded to 2 decimal places.

Table Structure

Table: Queries

Column Meaning

• query_name — the search term the user typed in (like “Dog” or “Cat”)
• result — the actual result that was shown for that query (like “Golden Retriever”)
• position — where this result appeared in the search rankings, from 1 (top) to 500 (bottom)
• rating — a user rating for that specific result, from 1 to 5. A rating below 3 is officially considered “poor”

Important Notes

• This table can contain duplicate rows. That means you should not assume every row is automatically unique. This mostly matters for how you think about counting, but since every calculation here is based on aggregating over query_name, duplicates naturally get included in the totals just like any other row would, there’s no special deduplication step needed for this particular problem.
• There are two completely separate metrics being asked for, and it’s easy to accidentally mix up their formulas if you’re not careful:
  • Quality = the average of (rating divided by position), calculated per row, then averaged across all rows for that query
  • Poor Query Percentage = what percentage of a query’s results have a rating below 3
• Both output numbers need to be rounded to exactly 2 decimal places.
• Since position ranges from 1 to 500 and is never 0, there’s no risk of a division-by-zero error here, which is worth noticing explicitly since dividing by position is central to this problem.

Example

Input: Queries table

Expected Output

CREATE TABLE Queries (query_name TEXT, result TEXT, position INTEGER, rating INTEGER);
INSERT INTO Queries VALUES
  ('Dog', 'Golden Retriever', 1, 5),
  ('Dog', 'German Shepherd', 2, 5),
  ('Dog', 'Mule', 200, 1),
  ('Cat', 'Shirazi', 5, 2),
  ('Cat', 'Siamese', 3, 3),
  ('Cat', 'Sphynx', 7, 4);

Explanation

Dog quality: (5/1 + 5/2 + 1/200) / 3 = (5 + 2.5 + 0.005) / 3 = 7.505 / 3 ≈ 2.50

Dog poor percentage: only 1 out of 3 Dog results has a rating below 3 (the “Mule” result, rated 1) → (1/3) × 100 = 33.33%

Cat quality: (2/5 + 3/3 + 4/7) / 3 = (0.4 + 1 + 0.5714) / 3 ≈ 0.66

Cat poor percentage: only 1 out of 3 Cat results has a rating below 3 (the “Shirazi” result, rated 2) → (1/3) × 100 = 33.33%

Intuition

Think about the “quality” metric first. Imagine a result that’s rated 5 out of 5, but sitting at position 1. Rating divided by position gives 5/1 = 5, a very high score. Now imagine that same 5-star result buried way down at position 500. Rating divided by position gives 5/500 = 0.01, an almost meaningless contribution. The formula naturally rewards good ratings at good (low-numbered) positions, and heavily discounts good ratings that are buried deep in the results.

Averaging this value across every result for a query gives you one number that reflects “on average, how good and how well-placed were this query’s results.”

The “poor query percentage” is a much simpler idea, it’s just asking: out of everything shown for this query, what fraction had a genuinely bad rating (below 3)? This is the exact same percentage pattern used back in Problem 2 (Instant Food Delivery), just applied per query instead of across the whole table.

Mental Model

Picture a teacher grading a stack of student essays for one class (one “query”). For quality, imagine each essay gets its own little score: how good the essay was, divided by how early it was turned in (position 1 being “turned in first”). A great essay turned in first scores very high. A great essay turned in very late scores much lower, because being late “dilutes” how impressive it is.

The teacher then averages all these individual scores together to get one overall quality number for the whole class.

Separately, and completely independently, the teacher also counts up how many essays failed outright (rating below 3), and turns that count into a percentage of the whole class.

Both numbers describe the same class, but they’re measuring two very different things, one is an averaged score, the other is a simple fail-rate percentage. This problem’s biggest conceptual trap is treating these two metrics as if they need the same formula, when really they need two completely separate calculations, just grouped by the same query_name.

Thought Process

Before writing SQL, separate the two metrics mentally, and solve them one at a time.

For Quality:

1. What do I calculate for a single row? rating / position.
2. How do I turn that into a per-query average? Add up rating / position across all rows for that query, then divide by how many rows that query has. This is exactly what AVG() would normally do, but since it’s a ratio calculated per row, you can either use AVG(rating * 1.0 / position) directly, or manually do SUM(rating * 1.0 / position) / COUNT(*), both are mathematically equivalent.
3. Do I need to group by query_name? Yes, since the average needs to be calculated separately for each query, not across the whole table.

For Poor Query Percentage:

1. What counts as “poor”? rating < 3.
2. How do I count only poor rows, per query? COUNT(CASE WHEN rating < 3 THEN 1 END), the same conditional counting pattern used in Problem 2.
3. How do I turn that count into a percentage? Divide by the total row count for that query (COUNT(*)), multiply by 100.

Combining both: Since both metrics need to be grouped by query_name, and both can be calculated using aggregate functions over the same grouped rows, they can both live inside a single SELECT statement with one shared GROUP BY query_name.

Approach

Step 1: Group by query_name Since both metrics need one result per query, use GROUP BY query_name.

Step 2: Calculate Quality Use SUM(rating * 1.0 / position) / COUNT(*). Multiplying by 1.0 forces the division to happen using decimal math instead of integer math (a similar concern to what came up in Problem 2 with integer division).

Step 3: Calculate Poor Query Percentage Use COUNT(CASE WHEN rating < 3 THEN 1 END) * 100.0 / COUNT(*). Again, multiplying by 100.0 (not just 100) helps ensure decimal division happens correctly.

Step 4: Round both results Wrap both calculations in ROUND(..., 2) to match the expected output format.

Step 5: Select and group Bring back query_name along with both calculated columns, grouped correctly.

SQL Query

SELECT
    query_name,
    -- Quality: average of (rating / position) across all rows for this query
    -- Multiplying by 1.0 ensures decimal division, not integer division
    ROUND(SUM(rating * 1.0 / position) / COUNT(*), 2) AS quality,
    -- Poor Query Percentage: percentage of rows with rating below 3
    -- COUNT(CASE WHEN...) counts only poor-rated rows, ignoring the rest via NULL
    ROUND(COUNT(CASE WHEN rating < 3 THEN 1 END) * 100.0 / COUNT(*), 2) AS poor_query_percentage
FROM
    Queries
GROUP BY
    query_name;

Query Explanation

• SUM(rating * 1.0 / position) — for every row within a query’s group, this calculates rating / position, and adds all of those individual ratios together. The 1.0 forces the division to produce a decimal result instead of potentially truncating to a whole number.
• / COUNT(*) — dividing the summed ratios by the total number of rows in that group gives the average ratio, which is exactly what “quality” is defined as.
• COUNT(CASE WHEN rating < 3 THEN 1 END) — counts only the rows where the rating is below 3. Rows that don’t meet this condition return NULL from the CASE (since there’s no ELSE), and COUNT automatically skips those, so only poor-rated rows get counted.
• 100.0 / COUNT(*) — converts the poor-row count into a percentage of the total rows for that query.
• ROUND(…, 2) — applied separately to both calculations, formatting each to exactly 2 decimal places.
• GROUP BY query_name — this is what makes every aggregate function (SUM, COUNT) calculate separately for each unique query, instead of across the entire table.

Dry Run

Original table

↓

Step 1: GROUP BY query_name forms two groups

↓

Step 2: Calculate rating/position for each row, within each group

↓

Step 3: Sum and average per group (Quality)

↓

Step 4: Count poor ratings (rating < 3) per group

↓

Final output

SQL Execution Flow

FROM Queries
   ↓
GROUP BY query_name (splits rows into per-query groups)
   ↓
Within each group: calculate rating/position per row, SUM them
   ↓
Divide summed ratios by COUNT(*) → quality
   ↓
Within each group: COUNT rows where rating < 3
   ↓
Divide poor count by COUNT(*), multiply by 100 → poor_query_percentage
   ↓
ROUND both calculated values to 2 decimal places
   ↓
SELECT query_name, quality, poor_query_percentage
   ↓
Return final output (order does not matter)

Both metrics are calculated within the same grouping pass, since GROUP BY only needs to happen once, and multiple different aggregate expressions can all be computed from that same grouped data simultaneously.

Time Complexity

Time Complexity: O(n log n), or closer to O(n) with hash-based grouping Similar to Problem 5, this query needs to group rows by query_name, which typically involves either sorting or hashing the data. Within each group, the per-row calculation (rating/position, and the rating < 3 check) is simple constant-time work, so the grouping step is what dominates the overall time complexity.

Space Complexity: O(q), where q is the number of distinct query names The database needs to maintain a running sum, running count, and running poor-count for each distinct query while scanning through the rows, so memory usage grows with the number of unique queries, not the total number of rows.

Why: Just like Problem 5, this is a grouped aggregation problem, not a simple per-row calculation. The database has to gather and combine values across multiple rows sharing the same query_name, and it needs to track two separate running calculations (the ratio sum for quality, and the poor-rating count for percentage) simultaneously for every group.

Common Mistakes to Avoid

Mistake 1: Using AVG(rating) / AVG(position) instead of AVG(rating / position)

Why it happens: It looks like these should be mathematically the same thing, but they are not.

Wrong:

ROUND(AVG(rating) / AVG(position), 2) AS quality

This calculates the average rating divided by the average position, which is a completely different number from the average of each row’s individual rating/position ratio. Averages don’t distribute across division the way they do across addition.

Correct:

ROUND(SUM(rating * 1.0 / position) / COUNT(*), 2) AS quality

Or equivalently, ROUND(AVG(rating * 1.0 / position), 2), since AVG() already divides the sum by the count internally.

Mistake 2: Forgetting decimal-forcing multiplication and losing precision

Why it happens: Some databases perform integer division by default when both operands are integers, silently dropping the decimal part.

Wrong (in databases where this matters):

ROUND(SUM(rating / position) / COUNT(*), 2) AS quality

If rating and position are both stored as integers, rating / position might get truncated to 0 for any row where rating is smaller than position (like 1/200), incorrectly recording that ratio as 0 instead of 0.005.

Correct:

ROUND(SUM(rating * 1.0 / position) / COUNT(*), 2) AS quality

Multiplying by 1.0 forces the division to happen in decimal, avoiding truncation.

Mistake 3: Using WHERE instead of CASE for the poor query count

Why it happens: It seems natural to filter for poor ratings directly.

Wrong:

SELECT query_name, COUNT(*) * 100.0 / (SELECT COUNT(*) FROM Queries q2 WHERE q2.query_name = q.query_name) AS poor_query_percentage
FROM Queries q
WHERE rating < 3
GROUP BY query_name;

This filters out all the non-poor rows before grouping, meaning the total count is lost and needs to be recovered with an extra correlated subquery, making the query far more complicated than it needs to be, and also risking dropping queries entirely from the output if none of their results happen to be poor.

Correct: Keep all rows in the main query, and use COUNT(CASE WHEN rating < 3 THEN 1 END) to count only the poor ones, exactly as shown in the main solution, so the total count (COUNT(*)) is naturally available in the same GROUP BY pass.

Mistake 4: Forgetting to round each metric separately

Why it happens: It’s easy to round only one of the two calculated columns and forget the other, especially if the query is written and tested incrementally.

Wrong:

SELECT
    query_name,
    SUM(rating * 1.0 / position) / COUNT(*) AS quality,
    ROUND(COUNT(CASE WHEN rating < 3 THEN 1 END) * 100.0 / COUNT(*), 2) AS poor_query_percentage
FROM Queries
GROUP BY query_name;

This rounds poor_query_percentage but leaves quality with potentially many decimal places, not matching the expected format.

Correct: Wrap both calculated expressions in their own ROUND(..., 2), as shown in the main solution.

Mistake 5: Not accounting for duplicate rows correctly

Why it happens: The problem explicitly warns that the table might contain duplicate rows, which can make some people assume they need to deduplicate first using DISTINCT.

Why it matters: If duplicates represent genuinely repeated log entries (the same result actually logged twice), removing them with DISTINCT would incorrectly shrink the row count and change both the quality average and the poor percentage. Since the problem doesn’t say duplicates are erroneous or should be ignored, they should be treated as valid rows and included normally in every calculation, exactly as this query already does by using COUNT(*) rather than COUNT(DISTINCT …).

Correct: Do not add DISTINCT unless the problem explicitly asks for deduplication. Let every row count naturally toward both metrics.

Further Questions

• “Why does AVG(rating) / AVG(position) give a different answer than AVG(rating / position)? Can you explain this with a small example?”
• “How would you modify this query to also show the total number of results per query?”
• “What would happen to the quality calculation if a query had only one result with a very high position number, like 500?”
• “How would you find the single query with the worst (lowest) quality score across the whole table?”
• “How would you handle this if position could theoretically be 0 or negative due to a data entry error?”

Similar Problems

• Problem 2 (Instant Food Delivery), which used the exact same COUNT(CASE WHEN …) percentage pattern, just without a GROUP BY
• Problem 5 (Apples vs Oranges), which used conditional aggregation combined with GROUP BY, similar in structure to this problem’s poor percentage calculation
• Weighted average problems, where a value needs to be adjusted by another related column (like rating adjusted by position) before being averaged

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• Need an average of a calculated ratio (like rating/position), not just an average of a single column → use SUM(ratio expression) / COUNT(*), or AVG(ratio expression) directly, never average the two columns separately and then divide
• Need both a percentage metric and a separate averaged metric, grouped the same way → both can live in the same SELECT with one shared GROUP BY, using different aggregate expressions
• Division involving two integer-type columns → force decimal division using 1.0 to avoid silent truncation
• Table explicitly mentions it may contain duplicate rows → don’t assume you need to deduplicate; only do so if the problem explicitly asks for it
• Multiple ROUND-ed output columns are expected → round each one individually, don’t assume rounding one covers the rest