Query Relevance Score and Low-Rating Share

EASY15 pts

A search analytics system records ranked results returned for each user query.

Table: queries

query_name: logical query group identifier
result: returned item label
position: rank position of that result in the list (1 is highest)
rating: evaluator score from 1 to 5

Data notes:

Duplicate rows may exist.
A row is considered poor if rating < 3.

For each query_name, compute two metrics:

quality: average of (rating / position)
poor_query_percentage: percentage of rows with rating < 3

Output requirements:

Return exactly these columns: query_name, quality, poor_query_percentage
Round both numeric metrics to 2 decimal places.
Result order can be arbitrary.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

queries:
| query_name | result            | position | rating |
|------------|-------------------|----------|--------|
| Dog        | Golden Retriever  | 1        | 5      |
| Dog        | German Shepherd   | 2        | 5      |
| Dog        | Mule              | 200      | 1      |
| Cat        | Persian           | 5        | 2      |
| Cat        | Siamese           | 3        | 3      |
| Cat        | Sphynx            | 7        | 4      |

Output

[
  {"query_name":"Cat","quality":0.66,"poor_query_percentage":33.33},
  {"query_name":"Dog","quality":2.5,"poor_query_percentage":33.33}
]

Explanation

For Dog, quality = ((5/1)+(5/2)+(1/200))/3 = 2.50 and one out of three rows is poor. For Cat, quality = ((2/5)+(3/3)+(4/7))/3 = 0.66 and one out of three rows is poor.

Example

Input

queries:
| query_name | result            | position | rating |
|------------|-------------------|----------|--------|
| Neon       | Item A            | 10       | 3      |
| Neon       | Item B            | 20       | 4      |
| Neon       | Item C            | 40       | 5      |
| Orion      | Item X            | 1        | 2      |
| Orion      | Item Y            | 2        | 2      |

Output

[
  {"query_name":"Neon","quality":0.26,"poor_query_percentage":0.0},
  {"query_name":"Orion","quality":1.5,"poor_query_percentage":100.0}
]

Explanation

Neon has no poor rows. Orion has two poor rows out of two, so poor_query_percentage is 100.00.

Example

Input

queries:
| query_name | result            | position | rating |
|------------|-------------------|----------|--------|
| Alpha      | Item 1            | 8        | 1      |
| Alpha      | Item 2            | 8        | 1      |
| Beta       | Item 3            | 500      | 5      |
| Beta       | Item 4            | 250      | 5      |
| Beta       | Item 5            | 250      | 1      |

Output

[
  {"query_name":"Alpha","quality":0.13,"poor_query_percentage":100.0},
  {"query_name":"Beta","quality":0.01,"poor_query_percentage":33.33}
]

Explanation

Alpha quality is 0.125, which rounds to 0.13. Beta has one poor row among three rows, so poor_query_percentage is 33.33.

Accepted0/0·0% Acceptance

Constraints

0 <= number of rows in queries <= 200000
1 <= position <= 500
1 <= rating <= 5
query_name is a non-empty string
result is a non-empty string
Duplicate rows may exist
A poor row is defined by rating < 3
Return exactly columns: query_name, quality, poor_query_percentage
Round both metrics to exactly 2 decimal places
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with exact required columns

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

queries =

[{"query_name":"Dog","result":"Golden Retriever","position":1,"rating":5},{"query_name":"Dog","result":"German Shepherd","position":2,"rating":5},{"query_name":"Dog","result":"Mule","position":200,"rating":1},{"query_name":"Cat","result":"Persian","position":5,"rating":2},{"query_name":"Cat","result":"Siamese","position":3,"rating":3},{"query_name":"Cat","result":"Sphynx","position":7,"rating":4}]

Query Relevance Score and Low-Rating Share

Hints

Query Relevance Score and Low-Rating Share

Hints