Skip to content

1211. Queries Quality and Percentage

DifficultyEasy

Description

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.

 

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.

When rounding to two decimal places, values exactly halfway between two possible results are rounded away from zero. For example, 0.125 is rounded to 0.13.

Return the result table in any order.

The result format is in the following example.

 

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 queries quality is ((5 / 1) + (5 / 2) + (1 / 200)) / 3 = 2.50
Dog queries poor_ query_percentage is (1 / 3) * 100 = 33.33

Cat queries quality equals ((2 / 5) + (3 / 3) + (4 / 7)) / 3 = 0.66
Cat queries poor_ query_percentage is (1 / 3) * 100 = 33.33

Solutions

Solution 1: Grouping and Aggregation

Thinking

Quality is the average of \(\textit{rating}/\textit{position}\); the poor-query rate is the fraction with \(\textit{rating} < 3\). Both are scalars per query name, so we \(\textit{GROUP BY}\ \textit{query\_name}\), take \(\textit{AVG}\) of the ratio and of the boolean, and \(\textit{ROUND}\) to two decimals. Null query names are dropped.

Group by \(\textit{query\_name}\), compute quality with AVG(rating / position), and compute the poor-query rate with AVG(rating < 3). Round both values to two decimal places, and drop null query names with WHERE query_name IS NOT NULL.

1
2
3
4
5
6
7
8
# Write your MySQL query statement below
SELECT
    query_name,
    ROUND(AVG(rating / position), 2) AS quality,
    ROUND(AVG(rating < 3) * 100, 2) AS poor_query_percentage
FROM Queries
WHERE query_name IS NOT NULL
GROUP BY 1;

Solution 2: Grouping and Aggregation (CASE Expression)

Thinking

Boolean \(\textit{AVG}\) is not equally clear in every dialect. Solution 2 \(\textit{CAST}\)s for decimal division and counts poor rows with \(\textit{CASE}\) over \(\textit{COUNT}(*)\). The result matches Solution 1 and is more portable.

The grouping is the same, and null query names are still dropped. quality uses \(\textit{CAST}\) so the division is decimal, and the poor-query rate is counted with a \(\textit{CASE}\) expression.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
# Write your MySQL query statement below
SELECT
    query_name,
    ROUND(AVG(CAST(rating AS DECIMAL) / position), 2) AS quality,
    ROUND(
        SUM(
            CASE
                WHEN rating < 3 THEN 1
                ELSE 0
            END
        ) / COUNT(*) * 100,
        2
    ) AS poor_query_percentage
FROM Queries
WHERE query_name IS NOT NULL
GROUP BY query_name;

Comments