Queries Quality and Percentage

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                 |
+------------+---------+-----------------------+