Difficulty: Easy | Acceptance: 53.00% | Paid: No Topics: Database
Table: Queries +-------------+---------+ | Column Name | Type | +-------------+---------+ | query_name | varchar | | result | varchar | | position | int | | rating | int | +-------------+---------+ This table may have duplicate rows. The query_name is the name of a query. The result is the result of the query. The position is the position of the result. The rating is the rating of the result.
Write a solution to calculate the quality and percentage of each query.
The quality of a query is the average rating of the query’s results. The percentage of a query is the number of results of the query divided by the total number of results, multiplied by 100.
Return the result table ordered by quality in descending order. In case of a tie, order them by query_name in ascending order.
The result format is in the following example.
- Examples
- Constraints
- Approach 1: Single-Pass Aggregation
- Approach 2: Two-Phase Aggregation
Examples
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 | percentage| +------------+---------+----------+ | Dog | 3.66 | 50.00 | | Cat | 3.00 | 50.00 | +------------+---------+----------+ Explanation:
- For query “Dog”, the average rating is (5 + 5 + 1) / 3 = 3.66, and the percentage is 3 / 6 * 100 = 50.00.
- For query “Cat”, the average rating is (2 + 3 + 4) / 3 = 3.00, and the percentage is 3 / 6 * 100 = 50.00.
Constraints
1 <= query_name.length <= 20
result.length <= 20
1 <= position <= 500
1 <= rating <= 5
Approach 1: Single-Pass Aggregation
Intuition Use a hash map to accumulate the sum of ratings and count for each query_name in a single pass, then compute quality and percentage.
Steps
- Iterate through all queries, accumulating sum and count per query_name
- Calculate total count during the same pass
- Compute quality (sum/count) and percentage (count/total * 100) for each query
- Sort by quality descending, then query_name ascending
import pandas as pd
def queries_quality(queries: pd.DataFrame) -> pd.DataFrame:
total = len(queries)
result = queries.groupby('query_name').agg(
quality=('rating', 'mean'),
count=('rating', 'count')
).reset_index()
result['percentage'] = (result['count'] / total * 100).round(2)
result = result.sort_values(by=['quality', 'query_name'], ascending=[False, True])
return result[['query_name', 'quality', 'percentage']]Complexity
- Time: O(n) where n is the number of rows in the Queries table
- Space: O(k) where k is the number of unique query names
- Notes: Single pass through data with hash map for aggregation
Approach 2: Two-Phase Aggregation
Intuition First aggregate sum and count by query_name, then calculate total count and compute final metrics in a second phase.
Steps
- Phase 1: Group by query_name and aggregate sum and count of ratings
- Phase 2: Calculate total count from aggregated data
- Compute quality and percentage for each query
- Sort by quality descending, then query_name ascending
import pandas as pd
def queries_quality(queries: pd.DataFrame) -> pd.DataFrame:
grouped = queries.groupby('query_name')['rating'].agg(['sum', 'count']).reset_index()
total = grouped['count'].sum()
grouped['quality'] = (grouped['sum'] / grouped['count']).round(2)
grouped['percentage'] = (grouped['count'] / total * 100).round(2)
result = grouped.sort_values(by=['quality', 'query_name'], ascending=[False, True])
return result[['query_name', 'quality', 'percentage']]Complexity
- Time: O(n) where n is the number of rows in the Queries table
- Space: O(k) where k is the number of unique query names
- Notes: Separates aggregation from computation for clearer code structure