Queries Quality and Percentage - Problem
You have a table Queries that contains information collected from database queries.
Table Structure:
query_name: The name of the queryresult: The result of the queryposition: Position value from 1 to 500rating: Rating value from 1 to 5
Definitions:
- Query Quality: The average of the ratio between query rating and its position
- Poor Query Percentage: The percentage of all queries with rating less than 3
Write a SQL query to find each query_name, its quality, and poor_query_percentage. Both values should be rounded to 2 decimal places.
Table Schema
Queries
| Column Name | Type | Description |
|---|---|---|
query_name
|
varchar | Name of the query |
result
|
varchar | Result of the query |
position
|
int | Position value from 1 to 500 |
rating
|
int | Rating value from 1 to 5 |
Primary Key: None
Note: Table may contain duplicate rows. Poor queries have rating < 3.
Input & Output
Example 1 — Basic Query Quality Calculation
Input 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.5 | 33.33 |
| Cat | 0.66 | 33.33 |
💡 Note:
For Dog: Quality = AVG(5/1, 5/2, 1/200) = AVG(5, 2.5, 0.005) = 2.50. Poor queries = 1 out of 3 = 33.33%
For Cat: Quality = AVG(2/5, 3/3, 4/7) = AVG(0.4, 1, 0.57) = 0.66. Poor queries = 1 out of 3 = 33.33%
Example 2 — All Good Queries
Input Table:
| query_name | result | position | rating |
|---|---|---|---|
| Search | Apple | 1 | 4 |
| Search | Banana | 2 | 3 |
| Filter | Orange | 3 | 5 |
Output:
| query_name | quality | poor_query_percentage |
|---|---|---|
| Search | 2.5 | 0 |
| Filter | 1.67 | 0 |
💡 Note:
For Search: Quality = AVG(4/1, 3/2) = AVG(4, 1.5) = 2.50. No poor queries = 0.00%
For Filter: Quality = 5/3 = 1.67. No poor queries = 0.00%
Constraints
-
1 ≤ position ≤ 500 -
1 ≤ rating ≤ 5 -
Poor queries have
rating < 3 - Results should be rounded to 2 decimal places
Visualization
Tap to expand
Understanding the Visualization
1
Input
Queries table with ratings and positions
2
GROUP BY
Group by query_name and aggregate
3
Output
Quality and poor query percentage per query
Key Takeaway
🎯 Key Insight: GROUP BY enables simultaneous calculation of multiple aggregate metrics per group
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code