Description
Write a SQL query to find the quality and percentage of poor queries for each query_name. Quality = avg(rating/position). Poor = rating < 3. **Output columns (in order):** query_name, quality, poor_query_percentage
Table:
queries| Column | Type |
|---|---|
| query_name | TEXT |
| result | TEXT |
| position | INT |
| rating | INT |
Examples
Input:
CREATE TABLE queries (query_name TEXT, result TEXT, position INT, rating INT); INSERT INTO queries VALUES ('Dog', 'Golden', 1, 5), ('Dog', 'German', 2, 2), ('Cat', 'Tabby', 1, 4);Output:
Cat|4.0|0.0
Dog|3.0|50.0Explanation:
Dog: Quality = (5/1 + 2/2)/2 = (5+1)/2 = 3.0. Poor = 1/2 = 50%. Cat: Quality = 4/1 = 4.0. Poor = 0%.
Input:
CREATE TABLE queries (query_name TEXT, result TEXT, position INT, rating INT); INSERT INTO queries VALUES ('Movie', 'Inception', 1, 1), ('Movie', 'Avatar', 2, 2), ('Movie', 'Titanic', 3, 4), ('Book', 'Novel', 1, 5), ('Book', 'Biography', 2, 3);Output:
Book|3.25|0.0
Movie|1.11|66.67Explanation:
Movie: Quality = (1/1 + 2/2 + 4/3)/3 = (1 + 1 + 1.33)/3 = 1.11. Poor queries = 2 out of 3 (ratings 1,2 < 3) = 66.67%. Book: Quality = (5/1 + 3/2)/2 = (5 + 1.5)/2 = 3.25. Poor queries = 0 out of 2 = 0.0%
Input:
CREATE TABLE queries (query_name TEXT, result TEXT, position INT, rating INT); INSERT INTO queries VALUES ('Weather', 'Sunny', 5, 1), ('Weather', 'Rainy', 3, 3), ('Weather', 'Cloudy', 2, 5), ('Game', 'Chess', 1, 2);Output:
Game|2.0|100.0
Weather|1.23|33.33Explanation:
Weather: Quality = (1/5 + 3/3 + 5/2)/3 = (0.2 + 1 + 2.5)/3 = 1.23. Poor queries = 1 out of 3 (only rating 1 < 3) = 33.33%. Game: Quality = 2/1 = 2.0. Poor queries = 1 out of 1 (rating 2 < 3) = 100.0%
Constraints
- •
Round to 2 decimal places