1211. Queries Quality and Percentage Solution

Q: Is Queries Quality and Percentage easy or hard?

Queries Quality and Percentage is classified as an Easy SQL problem on LeetCode with an acceptance rate around 43%. The challenge mainly tests familiarity with GROUP BY aggregation and conditional counting rather than complex algorithms.

Q: Queries Quality and Percentage Python/Java solution

In Python, load rows into a dictionary or pandas DataFrame and group by query_name. Compute the average of rating/position and the percentage of ratings below 3. Java implementations typically process rows with hash maps storing running totals and counts for each query.

Q: How to solve Queries Quality and Percentage in O(n)?

Scan the Queries table and group rows by query_name. For each group, compute AVG(rating * 1.0 / position) to get the quality and calculate SUM(CASE WHEN rating < 3 THEN 1 ELSE 0 END) divided by COUNT(*) multiplied by 100 to get the poor query percentage. SQL engines perform this aggregation in linear time relative to the number of rows.

Q: What is the best approach for Queries Quality and Percentage?

The best approach uses SQL aggregation with GROUP BY on query_name. Compute AVG(rating / position) for the quality metric and calculate the percentage of rows where rating < 3 using conditional aggregation. This solution runs in O(n) time and constant extra space because the database engine performs the grouping efficiently.

Q: Is Queries Quality and Percentage asked at Google/Amazon/Meta?

This problem represents a typical SQL analytics task commonly seen in database interviews at companies like Amazon, Google, and Meta. Candidates are expected to understand GROUP BY, aggregate functions, and conditional counting patterns.

Q: What data structure is used in Queries Quality and Percentage?

The main concept is relational aggregation rather than a traditional data structure. SQL GROUP BY groups rows by query_name, and aggregate functions like AVG, SUM, and COUNT compute the required metrics. In Python implementations, dictionaries or pandas DataFrames are commonly used to group data.

Q: What is the time complexity of Queries Quality and Percentage?

The time complexity is O(n) where n is the number of rows in the Queries table. The database scans the table once and aggregates values per query_name using GROUP BY. Space complexity is typically O(1) from the query perspective since only aggregated results are stored.

The problem

Table: Queries

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| query_name  | varchar |
| result      | varchar |
| position    | int     |
| rating      | int     |
+-------------+---------+
This table may have duplicate rows.
This table contains information collected from some queries on a database.
The position column has a value from 1 to 500.
The rating column has a value from 1 to 5. Query with rating less than 3 is a poor query.

We define query quality as:

The average of the ratio between query rating and its position.

We also define poor query percentage as:

The percentage of all queries with rating less than 3.

Write a solution to find each query_name, the quality and poor_query_percentage.

Both quality and poor_query_percentage should be rounded to 2 decimal places.

Return the result table in any order.

The result format is in the following example.

Example 1:

Input: 
Queries 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      |
+------------+-------------------+----------+--------+
Output: 
+------------+---------+-----------------------+
| query_name | quality | poor_query_percentage |
+------------+---------+-----------------------+
| Dog        | 2.50    | 33.33                 |
| Cat        | 0.66    | 33.33                 |
+------------+---------+-----------------------+
Explanation: 
Dog queries quality is ((5 / 1) + (5 / 2) + (1 / 200)) / 3 = 2.50
Dog queries poor_ query_percentage is (1 / 3) * 100 = 33.33

Cat queries quality equals ((2 / 5) + (3 / 3) + (4 / 7)) / 3 = 0.66
Cat queries poor_ query_percentage is (1 / 3) * 100 = 33.33

Approach overview

Problem Overview: You are given a Queries table where each row represents the result of a search query with its rating and position. For every query_name, compute two metrics: quality (the average of rating / position) and poor_query_percentage (percentage of rows where rating < 3). The output should group results by query_name and round values to two decimal places.

Approach 1: SQL Aggregation and Group By (O(n) time, O(1) space)

This problem fits naturally into SQL aggregation. Scan the table once, group rows by query_name, and compute two aggregates. For quality, calculate AVG(rating * 1.0 / position). For poor query percentage, use a conditional aggregation such as SUM(CASE WHEN rating < 3 THEN 1 ELSE 0 END) divided by the total count, multiplied by 100. SQL engines handle grouping efficiently, making this the cleanest and most common approach in interviews and production analytics queries.

Approach 2: SQL Aggregation with Subqueries (O(n) time, O(1) space)

Another way is to compute intermediate metrics in subqueries and combine them in an outer query. One subquery calculates the average rating / position per query_name, while another computes the percentage of poor queries. The outer query joins these results on query_name. This structure is useful when metrics are complex or derived from separate aggregations. It still performs a linear scan over the dataset and relies on relational aggregation features from database systems.

Approach 3: Using DataFrames or List Aggregations (O(n) time, O(n) space)

In Python environments such as analytics scripts or interview variants, load the data into a DataFrame or dictionary keyed by query_name. Iterate through rows, compute rating / position, and store values per query. Then compute the mean and the proportion of ratings below 3. Libraries like pandas provide groupby with vectorized aggregation, which mirrors SQL GROUP BY behavior. Space complexity increases because the dataset is held in memory.

Approach 4: Iterative Calculation After Data Retrieval (O(n) time, O(k) space)

If rows are retrieved through an API or cursor, process them iteratively. Maintain running totals for each query_name: sum of rating / position, total count, and count of rating < 3. After processing all rows, compute averages and percentages. This avoids storing all intermediate values and keeps only aggregated counters per query, which is efficient when working with streaming data.

Recommended for interviews: The expected solution is the SQL GROUP BY aggregation. It expresses both metrics directly with aggregate functions and conditional counting. Showing the subquery version demonstrates understanding of query decomposition, but the grouped aggregation is the most concise and idiomatic solution.

Approach 1: SQL Aggregation and Subqueries

This approach leverages SQL aggregation functions like SUM and COUNT along with subqueries to compactly compute the desired values. We will calculate the quality of each query and the percentage of poor queries by processing the data at the database level.

MySQL

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

We use SQL to compute the quality by dividing the sum of rating divided by position by the number of queries for each query_name. For the poor_query_percentage, we count how many ratings are less than 3, divide by the total number of queries, multiply by 100, and round to 2 decimal places.

Complexity

Time Complexity: O(n), where n is the number of rows, as we scan each row once.
Space Complexity: O(1), as we keep track of a fixed number of variables.

Try this approach in the editor

Approach 2: Using DataFrames or List Aggregations

This approach involves using programming languages like Python or Java to process the data after fetching it from the database. This method uses data structures such as lists or DataFrames to compute the results.

Python

import pandas as pd

# Assuming 'data' is a pandas DataFrame containing the Queries table
grouped = data.groupby('query_name')
result = []
for name, group in grouped:
    quality = (group['rating'] / group['position']).mean()
    poor_percentage = ((group['rating'] < 3).sum() / len(group)) * 100
    result.append([name, round(quality, 2), round(poor_percentage, 2)])
result_df = pd.DataFrame(result, columns=['query_name', 'quality', 'poor_query_percentage'])

In this approach, we use Python's pandas library to group the data by query_name. We then compute the quality as the mean of the rating divided by position for each group. To compute the poor_query_percentage, we count the number of entries with rating less than 3 and divide by the total number of entries in the group, then multiply by 100.

Complexity

Time Complexity: O(n), where n is the total number of entries in the table, as we perform constant-time operations on each entry.
Space Complexity: O(n), as we store grouped data and calculations for each query_name.

Try this approach in the editor

Approach 3: SQL Aggregation and Group By

The solution involves using SQL to group the data by query_name and then performing aggregation calculations:

Quality Calculation: Calculate the average of the ratio of the rating to its position for each query_name.
Poor Query Percentage: Compute the percentage of queries with a rating less than 3.

Using SQL's GROUP BY clause and functions like SUM, COUNT, and ROUND will facilitate these calculations.

MySQL

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

The query groups the Queries table by query_name and:

Calculates quality as the average of rating/position.
Determines poor_query_percentage by counting rows with rating < 3 and dividing by the total count, then multiplying by 100 to get a percentage.
ROUND is used to ensure the results are rounded to two decimal places.

Complexity

Time Complexity: O(n), where n is the number of rows. This is because each row is processed once.
Space Complexity: O(n) for the result set, but since SQL operations are typically conducted in-memory, space usage is generally constant.

Try this approach in the editor

Approach 4: Iterative Calculation After Data Retrieval

This approach involves fetching data from the Queries table and processing it in-memory using a programming language. This can be suitable when SQL capability is limited. The steps include:

Fetch all query records.
Use a dictionary or hashmap to group records by query_name.
For each query_name, compute the required values for quality and poor query percentage.

Python

import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# Create table and insert data
dataset = [
    ('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)
]
cursor.execute('''CREATE TABLE Queries (query_name TEXT, result TEXT, position INTEGER, rating INTEGER)''')
cursor.executemany('''INSERT INTO Queries VALUES (?, ?, ?, ?)''', dataset)
conn.commit()

# Fetching all data
data = {row[0]: [] for row in cursor.execute('SELECT query_name FROM Queries')}

for row in cursor.execute('SELECT * FROM Queries'):
    data[row[0]].append((row[2], row[3]))

results = []

for query_name in data:
    total_rating_by_position = 0
    poor_queries = 0
    total_queries = len(data[query_name])
    for position, rating in data[query_name]:
        total_rating_by_position += rating / position
        if rating < 3:
            poor_queries += 1
    quality = round(total_rating_by_position / total_queries, 2)
    poor_query_percentage = round((poor_queries / total_queries) * 100, 2)
    results.append((query_name, quality, poor_query_percentage))

conn.close()
print(results)

This Python solution uses SQLite to setup and process the data:

It creates an in-memory database and populates it with the dataset.
Data is fetched and grouped by query_name into a dictionary.
Iteratively, it computes the quality and poor_query_percentage by processing each group's data.

Complexity

Time Complexity: O(n), where n is the number of records since each record is processed once.
Space Complexity: O(n), due to the storage of grouped data and accumulated results in memory.

Try this approach in the editor

Complexity comparison

Approach	Complexity
SQL Aggregation and Subqueries	Time Complexity: O(n), where n is the number of rows, as we scan each row once. Space Complexity: O(1), as we keep track of a fixed number of variables.
Using DataFrames or List Aggregations	Time Complexity: O(n), where n is the total number of entries in the table, as we perform constant-time operations on each entry. Space Complexity: O(n), as we store grouped data and calculations for each query_name.
SQL Aggregation and Group By	Time Complexity: O(n), where n is the number of rows. This is because each row is processed once. Space Complexity: O(n) for the result set, but since SQL operations are typically conducted in-memory, space usage is generally constant.
Iterative Calculation After Data Retrieval	Time Complexity: O(n), where n is the number of records since each record is processed once. Space Complexity: O(n), due to the storage of grouped data and accumulated results in memory.

Complexity analysis

Approach	Time	Space	When to Use
SQL Aggregation and Group By	O(n)	O(1)	Standard SQL analytics queries; simplest and most common solution
SQL Aggregation with Subqueries	O(n)	O(1)	When metrics are computed in separate logical steps
DataFrames or List Aggregations (Python)	O(n)	O(n)	When solving outside SQL using pandas or in-memory processing
Iterative Calculation After Data Retrieval	O(n)	O(k)	Streaming data or cursor-based processing with limited memory

Frequently asked questions

Is Queries Quality and Percentage easy or hard?

Queries Quality and Percentage is classified as an Easy SQL problem on LeetCode with an acceptance rate around 43%. The challenge mainly tests familiarity with GROUP BY aggregation and conditional counting rather than complex algorithms.

Queries Quality and Percentage Python/Java solution

In Python, load rows into a dictionary or pandas DataFrame and group by query_name. Compute the average of rating/position and the percentage of ratings below 3. Java implementations typically process rows with hash maps storing running totals and counts for each query.

How to solve Queries Quality and Percentage in O(n)?

Scan the Queries table and group rows by query_name. For each group, compute AVG(rating * 1.0 / position) to get the quality and calculate SUM(CASE WHEN rating < 3 THEN 1 ELSE 0 END) divided by COUNT(*) multiplied by 100 to get the poor query percentage. SQL engines perform this aggregation in linear time relative to the number of rows.

What is the best approach for Queries Quality and Percentage?

The best approach uses SQL aggregation with GROUP BY on query_name. Compute AVG(rating / position) for the quality metric and calculate the percentage of rows where rating < 3 using conditional aggregation. This solution runs in O(n) time and constant extra space because the database engine performs the grouping efficiently.

Is Queries Quality and Percentage asked at Google/Amazon/Meta?

This problem represents a typical SQL analytics task commonly seen in database interviews at companies like Amazon, Google, and Meta. Candidates are expected to understand GROUP BY, aggregate functions, and conditional counting patterns.

What data structure is used in Queries Quality and Percentage?

The main concept is relational aggregation rather than a traditional data structure. SQL GROUP BY groups rows by query_name, and aggregate functions like AVG, SUM, and COUNT compute the required metrics. In Python implementations, dictionaries or pandas DataFrames are commonly used to group data.

What is the time complexity of Queries Quality and Percentage?

The time complexity is O(n) where n is the number of rows in the Queries table. The database scans the table once and aggregates values per query_name using GROUP BY. Space complexity is typically O(1) from the query perspective since only aggregated results are stored.

Queries Quality and Percentage solution and explanation

The problem

Approach overview

Approach 1: SQL Aggregation and Subqueries

Approach 2: Using DataFrames or List Aggregations

Approach 3: SQL Aggregation and Group By

Approach 4: Iterative Calculation After Data Retrieval

Complexity comparison

Complexity analysis

Frequently asked questions

Comments

Queries Quality and Percentage solution and explanation

The problem

Approach overview

Approach 1: SQL Aggregation and Subqueries

Approach 2: Using DataFrames or List Aggregations

Approach 3: SQL Aggregation and Group By

Approach 4: Iterative Calculation After Data Retrieval

Complexity comparison

Complexity analysis

Frequently asked questions

Comments

Related problems