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.