LeetCode 1211 - Queries Quality and Percentage
The problem gives us a database table named Queries. Each row represents the outcome of running a particular query against a database.
Difficulty: 🟢 Easy
Topics: Database
Solution
Problem Understanding
The problem gives us a database table named Queries. Each row represents the outcome of running a particular query against a database. The columns are:
| Column | Meaning |
|---|---|
query_name |
The name of the query group |
result |
The returned result string |
position |
The rank or position of the result |
rating |
A score from 1 to 5 evaluating the result quality |
The goal is to compute two metrics for every distinct query_name.
The first metric is called quality. For a given query group, we calculate the ratio:
$$\frac{\text{rating}}{\text{position}}$$
for every row belonging to that query, then take the average of all those ratios.
The second metric is called poor_query_percentage. A query is considered poor if its rating is less than 3. We must compute:
$$\frac{\text{number of poor queries}}{\text{total queries}} \times 100$$
for each query_name.
Finally, both values must be rounded to two decimal places.
The output should contain one row per unique query_name, along with its computed quality and poor_query_percentage.
The constraints are relatively small and straightforward. The position ranges from 1 to 500, and rating ranges from 1 to 5. The problem does not specify an enormous dataset, and because this is a database aggregation problem, the intended solution is to use SQL aggregation functions efficiently.
An important detail is that duplicate rows may exist. We should not remove duplicates because each row represents a valid query occurrence and contributes independently to the averages and percentages.
Another important edge case is when all rows for a query are poor queries, meaning the percentage becomes 100.00. Similarly, if none are poor, the percentage becomes 0.00. We must also ensure floating point division is used instead of integer division, otherwise the computed quality values would be incorrect.
Approaches
Brute Force Approach
A brute force solution would process each distinct query_name separately.
For every unique query name:
- Scan the entire table.
- Collect all rows belonging to that query.
- Compute the sum of
rating / position. - Count the total rows.
- Count how many rows have
rating < 3. - Compute the final averages and percentages.
This approach is correct because every query group is independently evaluated using the exact formulas from the problem statement. However, it repeatedly scans the entire table for every distinct query name.
If there are n rows and k distinct query names, this approach can require up to O(n * k) work. In the worst case where every row has a different query name, this becomes O(n^2).
Optimal Approach
The key observation is that all required values are aggregate statistics grouped by query_name.
Instead of repeatedly scanning the table, we can group rows by query_name once and compute:
- Average of
rating / position - Percentage of rows where
rating < 3
SQL aggregation functions are perfect for this:
AVG(rating / position)computes the quality.AVG(CASE WHEN rating < 3 THEN 1 ELSE 0 END) * 100computes the poor query percentage.
Because SQL databases are optimized for grouping and aggregation, this solution processes the table efficiently in a single grouped pass.
Approach Comparison
| Approach | Time Complexity | Space Complexity | Notes |
|---|---|---|---|
| Brute Force | O(n²) | O(1) | Repeatedly scans the table for each query name |
| Optimal | O(n) | O(k) | Uses grouping and aggregate calculations |
Algorithm Walkthrough
- Group all rows by
query_name.
We need one final output row per query name, so grouping naturally organizes all related rows together.
2. For each row in a group, compute rating / position.
This value contributes to the query quality metric. Since quality is defined as the average of these ratios, every row contributes equally. 3. Compute the average of all ratios in the group.
This gives the quality value for that query name.
4. Identify poor queries using the condition rating < 3.
Every poor query contributes 1, while non-poor queries contribute 0.
5. Compute the average of these indicator values and multiply by 100.
Since the average of zeros and ones equals the fraction of ones, multiplying by 100 converts it into a percentage.
6. Round both computed values to two decimal places.
The problem explicitly requires formatted numeric output with two decimal precision. 7. Return one row per query group.
Why it works
The algorithm works because both required metrics are averages over all rows belonging to the same query_name.
For quality:
$$\text{quality} = \frac{ \sum (\text{rating} / \text{position}) }{ \text{number of rows} }$$
For poor query percentage:
$$\text{poor percentage} = \frac{ \text{poor rows} }{ \text{total rows} } \times 100$$
Grouping ensures every row contributes exactly once to the correct query group, and aggregate functions compute these formulas directly.
Python Solution
# Write your MySQL query statement below
SELECT
query_name,
ROUND(AVG(rating / position), 2) AS quality,
ROUND(
AVG(CASE WHEN rating < 3 THEN 1 ELSE 0 END) * 100,
2
) AS poor_query_percentage
FROM Queries
GROUP BY query_name;
This solution uses SQL aggregation to compute both required metrics in a single grouped query.
The GROUP BY query_name clause ensures all rows for the same query are processed together.
The expression:
AVG(rating / position)
computes the average ratio required for the quality metric.
The CASE expression converts poor queries into 1 and non-poor queries into 0:
CASE WHEN rating < 3 THEN 1 ELSE 0 END
Taking the average of these values gives the fraction of poor queries. Multiplying by 100 converts the fraction into a percentage.
Finally, ROUND(..., 2) formats the results to two decimal places as required.
Go Solution
// There is no Go implementation for this problem because
// LeetCode 1211 is a SQL-only database problem.
Unlike algorithmic LeetCode problems, this database problem is solved entirely with SQL. Therefore, LeetCode does not provide a Go function signature or executable Go environment for this question.
Worked Examples
Example 1
Input table:
| query_name | result | position | rating |
|---|---|---|---|
| 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 |
Processing Dog
Compute ratios:
| Row | Formula | Value |
|---|---|---|
| 1 | 5 / 1 | 5.0 |
| 2 | 5 / 2 | 2.5 |
| 3 | 1 / 200 | 0.005 |
Average quality:
$$\frac{5 + 2.5 + 0.005}{3} = 2.501666...$$
Rounded:
$$2.50$$
Poor queries:
| Rating | Poor? |
|---|---|
| 5 | No |
| 5 | No |
| 1 | Yes |
Poor percentage:
$$\frac{1}{3} \times 100 = 33.33$$
Processing Cat
Compute ratios:
| Row | Formula | Value |
|---|---|---|
| 1 | 2 / 5 | 0.4 |
| 2 | 3 / 3 | 1.0 |
| 3 | 4 / 7 | 0.5714 |
Average quality:
$$\frac{0.4 + 1.0 + 0.5714}{3} = 0.6571$$
Rounded:
$$0.66$$
Poor queries:
| Rating | Poor? |
|---|---|
| 2 | Yes |
| 3 | No |
| 4 | No |
Poor percentage:
$$\frac{1}{3} \times 100 = 33.33$$
Final output:
| query_name | quality | poor_query_percentage |
|---|---|---|
| Dog | 2.50 | 33.33 |
| Cat | 0.66 | 33.33 |
Complexity Analysis
| Measure | Complexity | Explanation |
|---|---|---|
| Time | O(n) | Each row is processed once during aggregation |
| Space | O(k) | Storage for grouped query aggregates |
The database engine scans the table once and maintains aggregate values for each distinct query_name. If there are k unique query names, the grouping structure requires proportional storage.
Test Cases
# Example case from problem statement
assert round((5/1 + 5/2 + 1/200) / 3, 2) == 2.50
assert round((1/3) * 100, 2) == 33.33
# No poor queries
assert round((5/1 + 4/2) / 2, 2) == 3.50
assert round((0/2) * 100, 2) == 0.00
# All poor queries
assert round((1/1 + 2/2) / 2, 2) == 1.00
assert round((2/2) * 100, 2) == 100.00
# Single row query group
assert round((4/5), 2) == 0.80
assert round((0/1) * 100, 2) == 0.00
# Duplicate rows should count independently
assert round((5/1 + 5/1) / 2, 2) == 5.00
# Large position value
assert round((1/500), 2) == 0.00
# Mixed ratings
assert round((2/1 + 3/2 + 5/5) / 3, 2) == 1.50
assert round((1/3) * 100, 2) == 33.33
Test Case Summary
| Test | Why |
|---|---|
| Problem example | Validates standard functionality |
| No poor queries | Ensures percentage becomes 0 |
| All poor queries | Ensures percentage becomes 100 |
| Single row group | Tests minimal grouping size |
| Duplicate rows | Verifies duplicates are included |
| Large position value | Tests very small ratio calculations |
| Mixed ratings | Tests normal mixed-case aggregation |
Edge Cases
All Queries Are Poor
If every row for a query has rating < 3, then the poor query percentage should become 100.00.
A buggy implementation might accidentally divide integers incorrectly or mishandle percentage conversion. Using:
AVG(CASE WHEN rating < 3 THEN 1 ELSE 0 END) * 100
correctly produces 100.00 when all rows evaluate to 1.
No Poor Queries
If every rating is at least 3, then the percentage should become 0.00.
Some implementations incorrectly omit the ELSE 0 branch in the CASE statement, which can produce NULL values. Explicitly returning 0 guarantees the average works correctly.
Very Large Position Values
A position can be as large as 500, producing very small ratios such as:
$$\frac{1}{500} = 0.002$$
A careless implementation using integer division would incorrectly produce 0. SQL floating point division preserves decimal precision, and the final rounding step ensures the output format matches the requirements exactly.