Queries Quality and Percentage

2pts

Problem Statement

Search quality teams need to monitor how relevant search results are to users. They use two primary metrics: Quality and Poor Query Percentage.

Write an SQL query to find each query_name, its quality and poor_query_percentage.

Definitions:

Quality: The average of the ratio between query rating and its position.
Poor Query Percentage: The percentage of all queries with a rating less than 3.

Rules:

Both quality and poor_query_percentage should be rounded to 2 decimal places.
Return the result table in any order (sorted by query_name for consistency).
Results must be sorted by query_name in descending order.

Tests your understanding of

Basic SQL, Aggregation, Math Functions, Filtering and Rounding

Input Tables

queries

query_name(VARCHAR)	result(VARCHAR)	position(INTEGER)	rating(INTEGER)
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

Expected Output

query_name(VARCHAR)	quality(FLOAT)	poor_query_percentage(FLOAT)
Dog	2.5	33.33
Cat	0.66	33.33