Queries Quality and Percentage

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Description

You have a Queries table. Each row represents a search query result with a position and a rating. Define: - quality: the average of (rating / position) for each query_name, rounded to 2 decimal places. - poor_query_percentage: the percentage of results with rating < 3 for each query_name, rounded to 2 decimal places. Write a SQL query to report the query_name, quality, and poor_query_percentage for each query. Return the result in any order.

Database Schema

Queries

Column Name	Type	Description
query_name	VARCHAR	Name of the search query
result	VARCHAR	Result returned
position	INT	Position in results (1 to 500)
rating	INT	User rating (1 to 5)

Example

Queries

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

Explanation:

Dog quality = ((5/1)+(5/2)+(1/200))/3 = 2.50. Dog poor% = 1/3 * 100 = 33.33. Cat quality = ((2/5)+(3/3)+(4/7))/3 = 0.66. Cat poor% = 1/3 * 100 = 33.33.

Approach hint

Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.

Common mistake

Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.