Queries Quality and Percentage

Queries Quality and Percentage

What is this problem about?

The Queries Quality and Percentage SQL problem asks you to compute two metrics per query name: query quality (average of rating/position for each record) and poor query percentage (percentage of records with rating < 3). Round both to 2 decimal places. The database interview pattern tests conditional aggregation and average computation.

Why is this asked in interviews?

Microsoft, Meta, Amazon, Google, and Bloomberg ask this as a standard SQL conditional aggregation problem testing the CASE WHEN pattern inside aggregate functions combined with GROUP BY.

Algorithmic pattern used

GROUP BY + AVG + conditional COUNT.

SELECT query_name,
  ROUND(AVG(rating/position), 2) AS quality,
  ROUND(100.0 * SUM(CASE WHEN rating < 3 THEN 1 ELSE 0 END) / COUNT(*), 2) AS poor_query_percentage
FROM Queries
WHERE query_name IS NOT NULL
GROUP BY query_name

Example explanation

Query "Dog": records (5,1,4),(5,1,2),(4,2,4). Quality = (4/1+2/1+4/2)/3 = (4+2+2)/3 = 2.67. Poor = 1/3*100 = 33.33%.

Common mistakes candidates make

• Using integer division for quality (must use decimal: 1.0*rating/position).
• Not filtering NULL query_names.
• Wrong poor percentage formula (must be 100 × count_poor / total_count).
• Using WHERE rating < 3 instead of CASE WHEN.

Interview preparation tip

SQL conditional aggregation follows: SUM(CASE WHEN condition THEN 1 ELSE 0 END) for counting, AVG(expression) for average. Always multiply by 100.0 (not 100) for percentage to avoid integer division. Practice similar "compute multiple metrics per group" queries — they appear frequently in analytics SQL interviews.