Queries Quality and Percentage - Practice Coding Problems

Queries Quality and Percentage - Problem

Database Easy

You're working as a data analyst for a search engine company, analyzing the performance of database queries to improve user experience. Your task is to calculate two key metrics for each query type:

Quality Score: The average ratio of rating to position across all queries
Poor Query Percentage: The percentage of queries with ratings below 3

Given a Queries table with columns:

query_name (varchar): The name/type of the query
result (varchar): The search result returned
position (int): Position in search results (1-500)
rating (int): User rating (1-5, where <3 is considered poor)

Goal: Calculate quality and poor_query_percentage for each query_name, rounded to 2 decimal places.

Formula: Quality = AVG(rating/position), Poor% = (COUNT(rating<3)/COUNT(*))*100

Input & Output

example_1.sql — Basic Query Analysis

$ Input: Queries table: | query_name | result | position | rating | |------------|-------------------|----------|--------| | Dog | Golden Retriever | 1 | 5 | | Dog | German Shepherd | 2 | 5 | | Dog | Mule | 200 | 1 | | Cat | Shirazi | 5 | 2 | | Cat | Siamese | 3 | 3 | | Cat | Sphynx | 7 | 4 |

› Output: | query_name | quality | poor_query_percentage | |------------|---------|----------------------| | Cat | 2.66 | 33.33 | | Dog | 2.50 | 33.33 |

💡 Note: For Dog: quality = (5/1 + 5/2 + 1/200)/3 = 2.50, poor queries = 1 out of 3 = 33.33%. For Cat: quality = (2/5 + 3/3 + 4/7)/3 = 2.66, poor queries = 1 out of 3 = 33.33%.

example_2.sql — All Good Queries

$ Input: Queries table: | query_name | result | position | rating | |------------|------------|----------|--------| | FreshFood | Organic | 1 | 4 | | FreshFood | Local | 2 | 5 | | FreshFood | Seasonal | 3 | 3 |

› Output: | query_name | quality | poor_query_percentage | |------------|---------|----------------------| | FreshFood | 4.00 | 0.00 |

💡 Note: For FreshFood: quality = (4/1 + 5/2 + 3/3)/3 = 4.00, no poor queries (all ratings ≥ 3) = 0.00%.

example_3.sql — All Poor Queries

$ Input: Queries table: | query_name | result | position | rating | |------------|----------|----------|--------| | BadSearch | Spam1 | 1 | 1 | | BadSearch | Spam2 | 2 | 2 | | BadSearch | Spam3 | 3 | 1 |

› Output: | query_name | quality | poor_query_percentage | |------------|---------|----------------------| | BadSearch | 1.17 | 100.00 |

💡 Note: For BadSearch: quality = (1/1 + 2/2 + 1/3)/3 = 1.17, all queries are poor (ratings < 3) = 100.00%.

Constraints

query_name can be null or any varchar value
position is an integer from 1 to 500
rating is an integer from 1 to 5
Poor queries are defined as those with rating < 3
Results must be rounded to exactly 2 decimal places
Table may contain duplicate rows
Return results in any order

Visualization

Tap to expand

Understanding the Visualization

Group Reviews

Organize all reviews by product category (query_name)

Calculate Quality

For each category, compute average of (rating ÷ position) - higher ratings in better positions score higher

Count Poor Reviews

Count reviews with ratings below 3 stars as percentage of total

Generate Report

Round both metrics to 2 decimal places for presentation

Key Takeaway

🎯 Key Insight: Use SQL GROUP BY with aggregate functions (AVG, SUM, COUNT) to calculate multiple related metrics in a single efficient query, avoiding the need for multiple table scans.

Asked in

G Google 35 a Amazon 28 f Meta 22 ⊞ Microsoft 18

The optimal solution uses a single SQL query with GROUP BY to calculate both metrics simultaneously. Use AVG(rating * 1.0 / position) for quality and SUM(CASE WHEN rating < 3 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) for poor query percentage. The key insight is leveraging aggregate functions with conditional logic to avoid multiple table scans.

Common Approaches

Approach	Time	Space	Notes
✓ Multiple Query Approach	O(n×m)	O(m)	Calculate each metric with separate queries, then combine results
Single Query with GROUP BY (Optimal)	O(n)	O(m)	Calculate both metrics in one pass using SQL GROUP BY and aggregate functions

Multiple Query Approach — Algorithm Steps

Query 1: Calculate quality for each query_name using AVG(rating/position)
Query 2: Calculate poor query percentage using conditional counting
Join or combine the results from both queries
Round results to 2 decimal places

Visualization

Tap to expand

Step-by-Step Walkthrough

First Scan

Calculate quality metrics by scanning all data

Second Scan

Calculate poor query percentage by scanning again

Combine Results

Merge results from both scans

Code -

solution.c — C

// Simulating multiple query approach
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <math.h>

#define MAX_QUERIES 1000
#define MAX_NAME_LEN 100

typedef struct {
    char query_name[MAX_NAME_LEN];
    char result[MAX_NAME_LEN];
    int position;
    int rating;
} Query;

typedef struct {
    char query_name[MAX_NAME_LEN];
    double quality;
    double poor_query_percentage;
} Result;

int findUniqueQueries(Query queries[], int n, char uniqueNames[][MAX_NAME_LEN]) {
    int uniqueCount = 0;
    for (int i = 0; i < n; i++) {
        int found = 0;
        for (int j = 0; j < uniqueCount; j++) {
            if (strcmp(queries[i].query_name, uniqueNames[j]) == 0) {
                found = 1;
                break;
            }
        }
        if (!found) {
            strcpy(uniqueNames[uniqueCount], queries[i].query_name);
            uniqueCount++;
        }
    }
    return uniqueCount;
}

int calculateMetricsBruteForce(Query queries[], int n, Result results[]) {
    char uniqueNames[MAX_QUERIES][MAX_NAME_LEN];
    int uniqueCount = findUniqueQueries(queries, n, uniqueNames);
    
    for (int i = 0; i < uniqueCount; i++) {
        // Calculate quality
        double ratioSum = 0;
        int count = 0;
        for (int j = 0; j < n; j++) {
            if (strcmp(queries[j].query_name, uniqueNames[i]) == 0) {
                ratioSum += (double)queries[j].rating / queries[j].position;
                count++;
            }
        }
        
        // Calculate poor percentage
        int poorCount = 0;
        int totalCount = 0;
        for (int j = 0; j < n; j++) {
            if (strcmp(queries[j].query_name, uniqueNames[i]) == 0) {
                totalCount++;
                if (queries[j].rating < 3) poorCount++;
            }
        }
        
        strcpy(results[i].query_name, uniqueNames[i]);
        results[i].quality = round((ratioSum / count) * 100) / 100.0;
        results[i].poor_query_percentage = round(((double)poorCount / totalCount * 100) * 100) / 100.0;
    }
    
    return uniqueCount;
}

Time & Space Complexity

Time Complexity

⏱️

O(n×m)

Where n is number of rows and m is number of distinct query_names, as we scan the table multiple times

✓ Linear Growth

Space Complexity

O(m)

Space for storing intermediate results for m distinct query names

✓ Linear Space

23.5K Views

Medium Frequency

~15 min Avg. Time

856 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Multiple Query Approach — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler