Find the Product with the Highest Average Rating Query Using MS SQL Server?

To find the product with the highest average rating using Microsoft SQL Server, you need to:

1. Aggregate ratings for each product to calculate the average rating.
2. Identify the product with the highest average rating.

Assuming you have the following tables:

• Products table with columns: ProductID, ProductName.
• Ratings table with columns: ProductID, Rating (assuming the rating is a numeric value, e.g., from 1 to 5).

Here’s a step-by-step SQL query to find the product with the highest average rating:

SQL Query

-- Find the product with the highest average rating

WITH AverageRatings AS (

    SELECT

        p.ProductID,

        p.ProductName,

        AVG(r.Rating) AS AverageRating

    FROM

        Products p

        JOIN Ratings r ON p.ProductID = r.ProductID

    GROUP BY

        p.ProductID, p.ProductName

)

SELECT

    ProductID,

    ProductName,

    AverageRating

FROM

    AverageRatings

ORDER BY

    AverageRating DESC

LIMIT 1;

Explanation

1. AverageRatings CTE:

   • AVG(r.Rating) AS AverageRating: Calculates the average rating for each product.
   • JOIN Ratings r ON p.ProductID = r.ProductID: Joins the Products table with the Ratings table based on ProductID.
   • GROUP BY p.ProductID, p.ProductName: Groups by ProductID and ProductName to calculate the average rating per product.

2. Main Query:

   • ORDER BY AverageRating DESC: Orders the results by average rating in descending order.
   • LIMIT 1: Limits the result to the top product with the highest average rating.

Additional Considerations

• Handling Ties: If multiple products have the same highest average rating and you want to retrieve all such products, you can use a subquery or ROW_NUMBER() function instead of LIMIT 1:

WITH AverageRatings AS (

    SELECT

        p.ProductID,

        p.ProductName,

        AVG(r.Rating) AS AverageRating

    FROM

        Products p

        JOIN Ratings r ON p.ProductID = r.ProductID

    GROUP BY

        p.ProductID, p.ProductName

),

RankedProducts AS (

    SELECT

        ProductID,

        ProductName,

        AverageRating,

        ROW_NUMBER() OVER (ORDER BY AverageRating DESC) AS rn

    FROM

        AverageRatings

)

SELECT

    ProductID,

    ProductName,

    AverageRating

FROM

    RankedProducts

WHERE

    rn = 1;

• Data Quality: Ensure that the Ratings table has valid and meaningful ratings. Filter out invalid or missing ratings if necessary.

• Indexes: Indexes on ProductID in the Products and Ratings tables can help improve query performance, especially with large datasets.

Adjust the column names and table structure according to your actual schema. This query will help you identify the product with the highest average rating effectively.