1211 Queries Quality and Percentage

Description

Table: 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.

Problem Statement

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:

• Queries table:

Output:

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

Solution

This is an aggregation problem. In order to find quality, you need to find rating and position ration and then calculate average by getting sum of these ratio and divide it by total queries. In order to find poor query percentage, you need to divide the number of queries with rating less than 3 by the total number of queries.

1SELECT query_name,
2    ROUND(SUM(rating/position) / COUNT(1), 2) AS quality,
3    ROUND(SUM(CASE WHEN rating < 3 THEN 1 ELSE 0 END) / COUNT(1) * 100, 2) AS poor_query_percentage
4    FROM Queries
5    GROUP BY query_name