7. Query Quality Analysis

A search engine system logs query data including results, position in the search, and user ratings. Analysts want to evaluate query performance.

Given a Queries table with:

╔═════════════╦══════════╦══════════╦═════════╗
β•‘ query_name  β•‘ result   β•‘ position β•‘ rating  β•‘
╠═════════════╬══════════╬══════════╬═════════╣
β•‘ varchar     β•‘ varchar  β•‘ int      β•‘ int     β•‘
β•šβ•β•β•β•β•β•β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•β•
  • query_name: The name of the search query.
  • result: The search result returned.
  • position: The rank of the result (1 to 500).
  • rating: A rating score from 1 to 5 (rating < 3 is considered poor).

This table might contain duplicate rows.

You are required to calculate:

  • Query Quality: This is calculated as the average of (query rating / query position).
  • Poor Query Percentage: This is the percentage of queries where the rating is less than 3.

The result should display:

  • query_name
  • quality (rounded to 2 decimal places)
  • poor_query_percentage (rounded to 2 decimal places)

The result can be returned in any order.

Example 1:

Input:

Queries 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 β•‘
╠════════════╬════════╬═════════════════===═══╣
β•‘ Dog        β•‘ 2.50   β•‘ 33.33                 β•‘
β•‘ Cat        β•‘ 0.66   β•‘ 33.33                 β•‘
β•šβ•β•β•β•β•β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•===══╝

Explanation:

  • Dog quality - (5/1 + 5/2 + 1/200) / 3 = (5 + 2.5 + 0.005) / 3 β‰ˆ 2.50
  • Dog poor % - 1 poor result (1 rating) / 3 total = (1/3) Γ— 100 = 33.33%
  • Cat quality - (2/5 + 3/3 + 4/7) / 3 = (0.4 + 1 + 0.5714) / 3 β‰ˆ 0.66
  • Cat poor % - 1 poor result / 3 total = (1/3) Γ— 100 = 33.33%

Still unsure what the problem is asking ?

Let’s go through a few more examples, step by step, to make it clearer.

Hints

0
Β 
Test Case

Input:

Queries