Search Query Quality and Poor Query Percentage in SQL

Question

Given a `queries` table (query_name, result, position, rating), calculate `quality` as the average of `(rating / position)` and `poor_query_percentage` as the percentage of queries with rating < 3. Round both to 2 decimal places.

Accepted Answer

Optimal Solution:
SELECT 
  query_name,
  ROUND(AVG(rating::numeric / position), 2) AS quality,
  ROUND(100.0 * COUNT(CASE WHEN rating < 3 THEN 1 END) / COUNT(*), 2) AS poor_query_percentage
FROM queries
WHERE query_name IS NOT NULL
GROUP BY query_name
ORDER BY query_name ASC;

Technical Explanation:
The query groups by query_name, calculating the average rating/position ratio and conditional ratio of poor queries.

query_name	position	rating
Dog	1	5
Dog	2	5
Dog	200	1
Cat	1	5

query_name	quality	poor_query_percentage
Dog	2.5	33.33
Cat	5	0