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 query
  • result: The result of the query
  • position: Position value from 1 to 500
  • rating: 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
Cat 0.66 33.33
Dog 2.5 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
Filter 1.67 0
Search 2.75 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
Queries Quality and Percentage INPUT: Queries Table query_name result pos rating Dog Golden 1 5 Dog German 2 5 Dog Meli 200 1 Cat Shirazi 5 2 Cat Siamese 3 3 Cat Persian 2 1 Key Calculations: quality = AVG(rating/position) poor% = (rating<3)/total * 100 Group: Dog Group: Cat ALGORITHM STEPS 1 GROUP BY query_name Partition rows by query 2 Calculate Quality AVG(rating/position) Dog: (5/1 + 5/2 + 1/200)/3 = (5 + 2.5 + 0.005)/3 = 2.50 Cat: (2/5 + 3/3 + 1/2)/3 = 0.66 3 Calculate Poor % Count rating < 3 / total Dog: 1 poor / 3 total = 33.33% Cat: 2 poor / 3 total = 66.67% 4 ROUND to 2 decimals Format final output FINAL RESULT query_name quality poor_% Dog 2.50 33.33 Cat 0.66 66.67 SELECT query_name, ROUND(AVG(rating/ position),2) AS quality, ROUND(100*AVG(rating <3),2) AS poor_query_% GROUP BY query_name; OK - Complete Key Insight: The quality metric measures how relevant results appear by their position - higher ratings at lower positions mean better quality. Poor query percentage uses a boolean trick: rating < 3 returns 1 (true) or 0 (false), so AVG gives the percentage directly. TutorialsPoint - Queries Quality and Percentage | Optimal Solution

Related Problems

Asked in
Amazon 12 Facebook 8 Google 6
28.0K Views
Medium Frequency
~12 min Avg. Time
542 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen