Queries Quality and Percentage | LeetCode 1211

1. Problem Overview and Schema Definition

This chapter tackles LeetCode Problem 1211: Queries Quality and Percentage. The objective is to analyze a database table named queries that stores information collected from various search queries and calculate specific performance metrics grouped by each query name.

Table Schema (queries)

The table contains four primary columns with varying data types and business rules:

2. Core Business Metrics Defined

To successfully solve this problem, we must compute two distinct aggregation metrics for each unique query_name:

1. Query Quality: Defined as the average of the ratios between a query's rating and its position.

2. Poor Query Percentage: Defined as the percentage of all queries for a given query_name that have a rating strictly less than 3 ($rating < 3$).

Note: Both resulting metrics (quality and poor_query_percentage) must be rounded to exactly two decimal places. The final result table can be returned in any order.

3. Real-World Walkthrough and Numerical Example

Let us examine a sample dataset to understand how individual rows aggregate into final grouped outputs.

Sample Input Table (queries)

Step-by-Step Calculation for "Dog"

A. Quality Calculation

Compute individual ratios ($\frac{\text{rating}}{\text{position}}$) for each "Dog" row:

• Row 1: $\frac{5}{1} = 5.0$
• Row 2: $\frac{5}{2} = 2.5$
• Row 3: $\frac{1}{200} = 0.005$

Take the average across all "Dog" rows:

Rounded to 2 decimal places: 2.50

B. Poor Query Percentage Calculation

Identify whether each "Dog" row has a rating strictly less than 3:

• Row 1: Rating = $5 \not< 3 \rightarrow \text{Not Poor (0)}$
• Row 2: Rating = $5 \not< 3 \rightarrow \text{Not Poor (0)}$
• Row 3: Rating = $1 < 3 \rightarrow \text{Poor (1)}$

Calculate the percentage of poor queries:

Rounded to 2 decimal places: 33.33

4. Naive vs. Production-Ready Approaches

When dealing with conditional percentage calculations in SQL, developers often attempt complex multi-join subqueries or conditional filtering that breaks down when grouping.

The Naive Approach (Flawed)

Trying to filter rows where rating < 3 in a WHERE clause before grouping will completely ruin the denominator for the overall query count.

• Why it fails: If you filter out non-poor queries using WHERE rating < 3, your total count drops to only the poor queries, making it impossible to calculate an accurate percentage against all queries for that query_name.

The Production-Ready Conditional Aggregation Approach

Instead of filtering rows out, we use conditional mapping (IF or CASE WHEN) to assign weights (1 for poor, 0 for normal), and then take the AVG() of those weights multiplied by 100.

5. Step-by-Step SQL Query Construction

Let's build the final MySQL query piece by piece.

Step 1: Selecting and Grouping by Query Name

We need metrics per unique query name, so we group our dataset using GROUP BY.

SELECT 
    query_name
FROM queries
GROUP BY query_name;

Step 2: Calculating Quality

We apply the AVG() function to the division of rating by position.

SELECT 
    query_name,
    AVG(rating / position) AS quality
FROM queries
GROUP BY query_name;

Step 3: Calculating Poor Query Percentage

Using an IF() condition, we check if the rating is less than 3. If true, return 1; otherwise, return 0. Averaging these binary flags and multiplying by 100 yields the exact percentage.

SELECT 
    query_name,
    AVG(rating / position) AS quality,
    AVG(IF(rating < 3, 1, 0)) * 100 AS poor_query_percentage
FROM queries
GROUP BY query_name;

6. Complete Production Solution

Here is the final, optimized SQL query incorporating rounding to two decimal places using the ROUND() function.

SELECT 
    query_name,
    ROUND(AVG(rating / position), 2) AS quality,
    ROUND(AVG(IF(rating < 3, 1, 0)) * 100, 2) AS poor_query_percentage
FROM queries
GROUP BY query_name;

7. Deep Dive: How the Conditional Average Trick Works

The expression AVG(IF(rating < 3, 1, 0)) is a powerful idiom in SQL analytics.

Consider a query_name with $N$ total rows.

• Suppose $K$ of those rows have a rating less than 3.
• The IF statement evaluates to 1 exactly $K$ times and 0 the remaining $(N - K)$ times.
• The SUM of these values is simply $K$.
• Dividing by the total number of rows ($N$) via AVG() gives $\frac{K}{N}$, which is precisely the fraction of poor queries.
• Multiplying by 100 converts this fraction into a percentage.

8. Summary and Key Takeaways

• Understand the Formula: Always verify numerator and denominator definitions; quality is $\frac{\text{rating}}{\text{position}}$, while poor percentage is based on $rating < 3$.
• Avoid Premature Filtering: Never use a WHERE clause to filter out rows when calculating percentages against a total group population; use conditional expressions inside aggregate functions instead.
• Leverage Conditional Mapping: Use IF(condition, 1, 0) or CASE WHEN inside aggregate functions (AVG(), SUM()) to compute ratios and percentages cleanly in a single pass.
• Group Appropriately: Always include GROUP BY query_name when computing aggregate statistics per category.
• Precision Control: Use ROUND(val, 2) to satisfy output formatting requirements for financial or analytical reporting.