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
To solve Queries Quality and Percentage, the key idea is to use SQL aggregation with grouping. Each query name can appear multiple times with different positions and ratings, so we must compute metrics per query using GROUP BY query_name.
The query quality is calculated as the average of rating / position for all rows belonging to the same query. SQL aggregate functions such as AVG() help compute this efficiently.
The poor query percentage measures how often a query receives a low rating. This can be determined by counting rows where rating < 3, dividing by the total number of rows for that query, and multiplying by 100. Conditional aggregation using CASE WHEN inside SUM() is typically used for this calculation.
Finally, round both computed values to two decimal places and exclude rows where the query name is NULL. Since the table is scanned once and grouped by query name, the approach is efficient and scales well.
| Approach | Time Complexity | Space Complexity |
|---|---|---|
| SQL Aggregation with GROUP BY | O(n) | O(k) |
Everyday Data Science
Watch expert explanations and walkthroughs
Practice problems asked by these companies to ace your technical interviews.
Explore More ProblemsJot down your thoughts, approach, and key learnings
Poor query percentage is calculated by counting rows where the rating is less than 3 and dividing by the total number of rows for that query. This is typically implemented using SUM(CASE WHEN rating < 3 THEN 1 ELSE 0 END) divided by COUNT(*), then multiplied by 100.
This type of SQL aggregation problem is common in coding interviews, including those at large tech companies. It tests understanding of GROUP BY, conditional aggregation, and basic analytical calculations in SQL.
In database problems like this, SQL tables with aggregation functions act as the main data structure. Grouping by query_name and applying aggregate calculations allows efficient computation of metrics for each query.
The optimal approach uses SQL aggregation with GROUP BY on the query_name column. Functions like AVG, SUM, and conditional CASE expressions are used to compute query quality and poor query percentages efficiently in a single grouped query.