Query Relevance Score and Low-Rating Share

Easy

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 1

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 2

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 3

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.

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

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 |

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 |

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 |