# $1211. 查询结果的质量和占比
# https://leetcode-cn.com/problems/queries-quality-and-percentage/

# SQL架构
Create table If Not Exists Queries (query_name varchar(30), result varchar(50), position int, rating int);
Truncate table Queries;
insert into Queries (query_name, result, position, rating) values ('Dog', 'Golden Retriever', '1', '5');
insert into Queries (query_name, result, position, rating) values ('Dog', 'German Shepherd', '2', '5');
insert into Queries (query_name, result, position, rating) values ('Dog', 'Mule', '200', '1');
insert into Queries (query_name, result, position, rating) values ('Cat', 'Shirazi', '5', '2');
insert into Queries (query_name, result, position, rating) values ('Cat', 'Siamese', '3', '3');
insert into Queries (query_name, result, position, rating) values ('Cat', 'Sphynx', '7', '4');

# Write your MySQL query statement below
select
    query_name,
    round(avg(rating / position), 2) as quality,
    round(sum(if(rating < 3, 1, 0)) * 100 / count(*), 2) as poor_query_percentage
from
    Queries
group by
    query_name;

# clean-up
drop table Queries;