This Leetcode problem Queries Quality and Percentage LeetCode Solution is done in SQL.
List of all LeetCode Solution
Queries Quality and Percentage LeetCode Solution
Table of Contents
Problem Statement
Column Name | Type |
query_name | varchar |
result | varchar |
position | int |
rating | int |
Queries
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.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input:
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
Queries Quality and Percentage LeetCode Solution MySQL
select
q.query_name,
round(
ifnull(
avg(rating / position),
0
),
2
) as quality,
round(
ifnull(
cnt / count(q.rating) * 100,
0
),
2
) as poor_query_percentage
from
Queries as q
left join (
select
query_name,
count(*) as cnt
from
Queries
where
rating < 3
group by
query_name
) as p on q.query_name = p.query_name
group by
q.query_name;
Code language: SQL (Structured Query Language) (sql)
Queries Quality and Percentage LeetCode Solution MySQL (Another approach)
select
query_name,
round(
avg(rating / position),
2
) as quality,
round(
avg(
if(rating < 3, 1, 0)
) * 100,
2
) as poor_query_percentage
from
Queries
group by
query_name;
Code language: SQL (Structured Query Language) (sql)