1211. Queries Quality and Percentage
SQL 50 · Basic Aggregate Functions (day 3). Decode it with the hints, solve it on LeetCode, then compare with the reference solution in MySQLPostgreSQL.
Your work
Hints
Open one at a time. Try to finish with as few as you can.
1. What does one output row represent?
One query_name.
2. Approach
GROUP BY query_name, ROUND(AVG(rating / position), 2), ROUND(AVG(rating < 3) * 100, 2).GROUP BY query_name, ROUND(AVG(rating::numeric / position), 2), ROUND(AVG((rating < 3)::int) * 100, 2).
3. The trap
Both are averages over the same rows; no WHERE needed.rating / position is integer division in PostgreSQL. Cast before dividing.
Reference solution
Tested on MySQL 8.0 and PostgreSQL 16. Switch dialect at the top of the page.
Show the MySQLPostgreSQL solution
SELECT query_name, ROUND(AVG(rating / position), 2) AS quality, ROUND(AVG(rating < 3) * 100, 2) AS poor_query_percentage FROM Queries GROUP BY query_name;SELECT query_name, ROUND(AVG(rating::numeric / position), 2) AS quality, ROUND(AVG((rating < 3)::int) * 100, 2) AS poor_query_percentage FROM Queries GROUP BY query_name;