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. Thepositioncolumn has a value from 1 to 500. Theratingcolumn has a value from 1 to 5. Query withratingless 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
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.
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.
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.
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.
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.
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.
The solution involves using SQL to group the data by query_name and then performing aggregation calculations:
rating to its position for each query_name.rating less than 3.Using SQL's GROUP BY clause and functions like SUM, COUNT, and ROUND will facilitate these calculations.
The query groups the Queries table by query_name and:
quality as the average of rating/position.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.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.
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:
query_name.This Python solution uses SQLite to setup and process the data:
query_name into a dictionary.quality and poor_query_percentage by processing each group's data.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.
| Approach | Complexity |
|---|---|
| SQL Aggregation and Subqueries | Time Complexity: O(n), where n is the number of rows, as we scan each row once. |
| 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. |
| SQL Aggregation and Group By | Time Complexity: O(n), where n is the number of rows. This is because each row is processed once. |
| Iterative Calculation After Data Retrieval | Time Complexity: O(n), where n is the number of records since each record is processed once. |
LeetCode 1211 Interview SQL Question with Detailed Explanation | Practice SQL • Everyday Data Science • 13,812 views views
Watch 9 more video solutions →Practice Queries Quality and Percentage with our built-in code editor and test cases.
Practice on FleetCode