Flipkart mid JOINGROUP BYAVGCorrelated SubquerySUMHAVINGORDER BY

Find suppliers whose average product unit price is greater than the average unit price of all suppliers in the same category. Return the supplier name, category, and average unit price. Sort the results by category and then by average unit price in descending order.

Flipkart · Supplier Analytics — practice this real-world SQL scenario live in your browser.

📖 Story Flipkart · Supplier Analytics

Tuesday morning at Flipkart's Procurement office. The Supplier Relations team is identifying which suppliers are strategically important for the upcoming mega sale. Instead of evaluating every supplier's performance individually, they want to find suppliers whose average product prices are higher than the average prices in their category. These premium-positioned suppliers can help Flipkart create exclusive, high-value product collections.

🎯 Your Mission

📋 Table Structure

🗂 suppliers

supplier_id INTEGER 201

supplier_name TEXT TechGear Co

category TEXT Electronics

country TEXT India

🗂 inventory

inventory_id INTEGER 1001

supplier_id INTEGER 201

product_name TEXT Wireless Headphones

unit_price DECIMAL 2500

stock_quantity INTEGER 150

⚡ Step-by-Step Walkthrough

Calculate the average unit price for each supplier

query.sql

SELECT s.supplier_id,
       s.supplier_name,
       s.category,
       AVG(i.unit_price) AS avg_unit_price
FROM suppliers s
JOIN inventory i
ON s.supplier_id = i.supplier_id
GROUP BY s.supplier_id,
         s.supplier_name,
         s.category
ORDER BY s.supplier_id;

💡 Explanation

The suppliers table stores supplier details like name and category, while the inventory table stores individual product records with their unit prices. To find suppliers with premium pricing strategies, we first need to calculate each supplier's average product price.
JOIN connects each product in inventory with its corresponding supplier. Without this JOIN, we would know the unit prices but wouldn't know which supplier they belong to.
AVG(i.unit_price) calculates the average of all unit prices for products supplied by a single supplier.
GROUP BY s.supplier_id combines multiple products from the same supplier into a single row. Without GROUP BY, SQL cannot calculate a separate average for each supplier.
At the end of this step, each supplier has one row showing their average unit price. We are not comparing suppliers yet. We are simply preparing the base metric that will be used in the next step.
Run this query and verify that suppliers like TechGear Co have different average prices from other suppliers in the same Electronics category. These averages will be compared against the category average in the next step.

Calculate the average unit price for all suppliers in the same category

query.sql

SELECT s.supplier_id,
       s.supplier_name,
       s.category,
       AVG(i.unit_price) AS avg_unit_price,
       (
           SELECT AVG(unit_price_inner)
           FROM (
               SELECT AVG(i2.unit_price) AS unit_price_inner
               FROM suppliers s2
               JOIN inventory i2
               ON s2.supplier_id = i2.supplier_id
               WHERE s2.category = s.category
               GROUP BY s2.supplier_id
           ) category_averages
       ) AS category_avg_price
FROM suppliers s
JOIN inventory i
ON s.supplier_id = i.supplier_id
GROUP BY s.supplier_id,
         s.supplier_name,
         s.category
ORDER BY s.category, avg_unit_price DESC;

💡 Explanation

From the previous step, we already know each supplier's average unit price. Now we need to calculate the average unit price for all suppliers within the same category to use as a benchmark.
The correlated subquery calculates this category average. It runs once for every row returned by the outer query. As the outer query moves from one supplier to another, the value of s.category changes, and the subquery recalculates the category average accordingly.
Inside the subquery, we first calculate the average unit price for every supplier within the current category. We then calculate the average of those supplier averages to get the category's overall average price.
Notice the condition WHERE s2.category = s.category. Here, s2.category belongs to the inner query, while s.category comes from the outer query. This reference to the outer query is what makes it a correlated subquery.
We are not filtering any suppliers yet. The purpose of this step is simply to display two values for every supplier: its own average unit price and its category's average unit price. These two values will be compared in the next step.
Run this query and verify that you can see both the individual supplier's average price and the category average for comparison in the next step.

Filter suppliers whose average unit price exceeds their category's average

query.sql

SELECT s.supplier_name,
       s.category,
       AVG(i.unit_price) AS avg_unit_price
FROM suppliers s
JOIN inventory i
ON s.supplier_id = i.supplier_id
GROUP BY s.supplier_id,
         s.supplier_name,
         s.category
HAVING AVG(i.unit_price) > (
    SELECT AVG(unit_price_inner)
    FROM (
        SELECT AVG(i2.unit_price) AS unit_price_inner
        FROM suppliers s2
        JOIN inventory i2
        ON s2.supplier_id = i2.supplier_id
        WHERE s2.category = s.category
        GROUP BY s2.supplier_id
    ) category_averages
)
ORDER BY s.category, avg_unit_price DESC;

💡 Explanation

In the previous step, every supplier had two important values: its own average unit price and the average unit price of all suppliers in the same category. The final task is simply to compare these two values and keep only the suppliers whose average exceeds their category's average.
HAVING is used instead of WHERE because avg_unit_price is calculated using the AVG() aggregate function. Since SQL evaluates WHERE before GROUP BY, aggregated values are not available inside the WHERE clause. HAVING evaluates conditions after GROUP BY, so it can use aggregate functions.
For every supplier, SQL first calculates its average unit price using AVG(i.unit_price). It then executes the correlated subquery to calculate the average unit price of all suppliers belonging to the same category.
If a supplier's average unit price is greater than its category's average unit price, the supplier is included in the final result. Suppliers with average prices equal to or below the category average are excluded.
ORDER BY s.category, avg_unit_price DESC organizes the output by category and lists the premium-priced suppliers first within each category.
Business Insight: These suppliers represent the premium positioning in their categories. Flipkart can partner with these suppliers to create exclusive product collections, offer luxury tier products, or target high-value customer segments. These are strategic suppliers for value-added offerings.

▶ Practice this live ← Browse all questions