Queries Quality And Percentage Problem
Description
LeetCode Problem 1211.
Table: Queries
1
2
3
4
5
6
7
8
9
10
11
12
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| query_name | varchar |
| result | varchar |
| position | int |
| rating | int |
+-------------+---------+
There is no primary key for this table, it 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 an SQL query to find each query_name, the quality and poor_query_percentage.
Both quality and poor_query_percentage should be rounded to 2 decimal places.
The query result format is in the following example:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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 |
+------------+-------------------+----------+--------+
Result table:
+------------+---------+-----------------------+
| query_name | quality | poor_query_percentage |
+------------+---------+-----------------------+
| Dog | 2.50 | 33.33 |
| Cat | 0.66 | 33.33 |
+------------+---------+-----------------------+
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
MySQL Solution
1
2
3
4
5
select query_name,
round(sum(rating/position)/count(query_name), 2) as quality,
round(100*sum(if(rating < 3, 1, 0))/count(query_name), 2) as poor_query_percentage
from Queries
group by query_name
LeetCode Database - Easy
LeetCode 1050
LeetCode 1068
LeetCode 1069
LeetCode 1075
LeetCode 1076
LeetCode 1082
LeetCode 1083
LeetCode 1084
LeetCode 1113
LeetCode 1141
LeetCode 1142
LeetCode 1148
LeetCode 1173
LeetCode 1179
LeetCode 1211
LeetCode 1241
LeetCode 1251
LeetCode 1280
LeetCode 1294
LeetCode 1303
LeetCode 1322
LeetCode 1327
LeetCode 1350
LeetCode 1378
More LeetCode Database
MySQL Tutorials