Objective

Construct an SQL query to fetch all products that are currently available in stock and priced above the average price of all available products. The resulting list should be sorted primarily by price in descending order and secondarily by rating in descending order, ensuring that products without a rating appear at the end.

Additional information

• Only include products where stock_quantity is greater than 0.
• The average price should be calculated based only on products that are in stock.
• When sorting, products with a NULL rating should be listed after those with non-null ratings.
• The query should return all columns from the Products table.
• Handle scenarios where some products might not have a rating value.

Products  
--------  
product_id INT  
name VARCHAR  
price DECIMAL(10,2)  
stock_quantity INT  
rating DECIMAL(3,2) NULL

Explanation

The objective is to retrieve all products that are currently available in stock and priced above the average price of all available products. Additionally, the results should be organized in a way that the most expensive products appear first, and among products with the same price, those with higher ratings come before those with lower or no ratings. Products without a rating should be listed at the end.

Step-by-Step Solution

Let's break down the SQL query step by step to understand how it achieves the desired outcome:

SELECT * 
FROM Products 
WHERE price > (
    SELECT AVG(price) 
    FROM Products 
    WHERE stock_quantity > 0
) 
AND stock_quantity > 0 
ORDER BY price DESC, rating DESC NULLS LAST;

1. SELECT * FROM Products:

   • This part selects all columns from the Products table. It means that the final output will include every detail available for each product.

2. WHERE price > (...) AND stock_quantity > 0:

   • The WHERE clause filters the products based on two conditions:
     • stock_quantity > 0: Ensures that only products that are currently in stock are considered.
     • price > (...): Further narrows down the selection to products whose price is higher than a certain value, which is determined by the subquery.

3. Subquery: SELECT AVG(price) FROM Products WHERE stock_quantity > 0:

   • This inner query calculates the average price of all products that are in stock.
   • AVG(price): Computes the average price.
   • WHERE stock_quantity > 0: Ensures that the average is calculated only from products that are available in stock.

4. Combining the Conditions:

   • By placing the subquery within the main WHERE clause, the query selects only those products that are both in stock and priced above the calculated average price of in-stock products.

5. ORDER BY price DESC, rating DESC NULLS LAST:

   • ORDER BY price DESC: Sorts the resulting products in descending order of their price, so the most expensive products appear first.
   • rating DESC: Within the same price, products are further sorted in descending order of their rating, ensuring higher-rated products come before lower-rated ones.
   • NULLS LAST: Ensures that products without a rating (i.e., where the rating is NULL) appear after all products that have a rating.

This structured approach ensures that the query efficiently filters, calculates, and sorts the products to meet the specified requirements.

SELECT * 
FROM Products 
WHERE price > (
    SELECT AVG(price) 
    FROM Products 
    WHERE stock_quantity > 0
) 
AND stock_quantity > 0 
ORDER BY price DESC, rating DESC NULLS LAST;