queries-quality-and-percentage

java

python

python3

mysql

mssql

oraclesql

csharp

javascript

typescript

bash

swift

kotlin

dart

golang

ruby

scala

rust

racket

erlang

elixir

pythondata

postgresql

cangjie

Database

Table: <code>Queries</code>

<pre>
+-------------+---------+
| 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 <code>position</code> column has a value from 1 to 500.
The <code>rating</code> column has a value from 1 to 5. Query with <code>rating</code> less than 3 is a poor query.
</pre>

&nbsp;

We define query <code>quality</code> as:

<blockquote>
The average of the ratio between query rating and its position.
</blockquote>

We also define <code>poor query percentage</code> as:

<blockquote>
The percentage of all queries with rating less than 3.
</blockquote>

Write a solution to find each <code>query_name</code>, the <code>quality</code> and <code>poor_query_percentage</code>.

Both <code>quality</code> and <code>poor_query_percentage</code> should be rounded to 2 decimal places.

Return the result table in any order.

The&nbsp;result format is in the following example.

&nbsp;
Example 1:

<pre>
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
</pre>

queries_stats

Queries Quality and Percentage

Percentage of Users Attended a Contest

percentage-of-users-attended-a-contest

各赛事的用户注册率

<code>Queries</code>&nbsp;表：&nbsp;

<pre>
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| query_name | varchar |
| result | varchar |
| position | int |
| rating | int |
+-------------+---------+
此表可能有重复的行。
此表包含了一些从数据库中收集的查询信息。
“位置”（<code>position</code>）列的值为 1 到 500 。
“评分”（<code>rating</code>）列的值为 1 到 5 。评分小于 3 的查询被定义为质量很差的查询。
</pre>

&nbsp;

将查询结果的质量 <code>quality</code> 定义为：

<blockquote>
各查询结果的评分与其位置之间比率的平均值。
</blockquote>

将劣质查询百分比&nbsp;<code>poor_query_percentage</code>&nbsp;定义为：

<blockquote>
评分小于 3 的查询结果占全部查询结果的百分比。
</blockquote>

编写解决方案，找出每次的&nbsp;<code>query_name</code>&nbsp;、&nbsp;<code>quality</code>&nbsp;和&nbsp;<code>poor_query_percentage</code>。

<code>quality</code>&nbsp;和&nbsp;<code>poor_query_percentage</code>&nbsp;都应 四舍五入到小数点后两位 。

以 任意顺序 返回结果表。

结果格式如下所示：

&nbsp;

示例 1：

<pre>
输入：
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 |
+------------+-------------------+----------+--------+
输出：
+------------+---------+-----------------------+
| query_name | quality | poor_query_percentage |
+------------+---------+-----------------------+
| Dog | 2.50 | 33.33 |
| Cat | 0.66 | 33.33 |
+------------+---------+-----------------------+
解释：
Dog 查询结果的质量为 ((5 / 1) + (5 / 2) + (1 / 200)) / 3 = 2.50
Dog 查询结果的劣质查询百分比为 (1 / 3) * 100 = 33.33

Cat 查询结果的质量为 ((2 / 5) + (3 / 3) + (4 / 7)) / 3 = 0.66
Cat 查询结果的劣质查询百分比为 (1 / 3) * 100 = 33.33
</pre>