0% completed
Problem
Table: Queries
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| query_name | varchar |
| result | varchar |
| position | int |
| rating | int |
+-------------+---------+
This table may have duplicate rows.
This table contains information collected from some queries on a database.
The position column has a value from 1 to 500.
The rating column has a value from 1 to 5. Query with rating less than 3 is a poor query.
Problem Definition
We define query quality
as: The average of the ratio between query rating and its position.
We also define poor query percentage
as: The percentage of all queries with rating less than 3.
Write a solution to find each query_name
, the quality
and poor_query_percentage
. Both quality
and poor_query_percentage
should be rounded to 2 decimal places.
Example
Output
Try It Yourself
Solution
The focus is on calculating the average quality of each query and identifying the percentage of queries with poor results. The query utilizes the ROUND
function for precision and employs conditional logic with the IF
statement to assess query ratings.
The results are grouped by query name using the GROUP BY
clause, providing insights into individual query performance characteristics.
SELECT query_name, Round(Avg(rating / position), 2) AS quality, Round(Sum(IF(rating < 3, 1, 0)) / Count(*) * 100, 2) AS poor_query_percentage FROM Queries GROUP BY query_name;
Let's break down the query step by step.
Step 1: Calculate the quality for each query.
ROUND(AVG(rating / position), 2) AS quality
Calculating the average quality for each query_name
:
- For Dog: (5/1 + 5/2 + 1/200) / 3 = 2.50
- For Cat: (2/5 + 3/3 + 4/7) / 3 = 0.66
Output After Step 1:
+------------+---------+ | query_name | quality | +------------+---------+ | Dog | 2.50 | | Cat | 0.66 | +------------+---------+
Step 2: Calculate Poor Query Percentage
ROUND(sum( IF(rating < 3, 1, 0)) / count(*) * 100, 2) AS poor_query_percentage
Counting the percentage of poor queries (rating < 3) for each query_name
:
- For Dog: (1 < 3) / 3 * 100 = 33.33%
- For Cat: (1 < 3 + 0 + 0) / 3 * 100 = 33.33%
Output After Step 2:
+------------+-----------------------+ | query_name | poor_query_percentage | +------------+-----------------------+ | Dog | 33.33 | | Cat | 33.33 | +------------+-----------------------+
Step 3: Combining the results
SELECT query_name, Round(Avg(rating / position), 2) AS quality, Round(Sum(IF(rating < 3, 1, 0)) / Count(*) * 100, 2) AS poor_query_percentage FROM Queries GROUP BY query_name;
Combining the results from Steps 1 and 2 into the final output:
Final Output:
+------------+---------+-----------------------+ | query_name | quality | poor_query_percentage | +------------+---------+-----------------------+ | Dog | 2.50 | 33.33 | | Cat | 0.66 | 33.33 | +------------+---------+-----------------------+
.....
.....
.....
Table of Contents
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible