SQL Interview Questions – Queries Quality and Percentage

Problem Description –

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.

Return the result table in any order.

The query result format is in the following example.

Difficulty Level – Easy

Problem Link – Queries Quality and Percentage

Solution –

WITH temp as 
(
    SELECT
        query_name,
        result,
        position,
        rating,
        rating / position as rat_pos,
        (CASE WHEN rating < 3 THEN 1 ELSE 0 END) as is_poor
    FROM Queries
)

SELECT
    query_name,
    ROUND(SUM(rat_pos) / COUNT(*),2) as quality,
    ROUND(SUM(is_poor) *100 / COUNT(*), 2) as poor_query_percentage
FROM temp
GROUP BY 1