Myntra mid JOINGROUP BYSUMCorrelated SubqueryAVGHAVINGORDER BY

Find products whose total sales quantity is greater than the average total sales quantity of products from the same brand. Return the product name, brand and total sales quantity. Sort the result by brand and total sales quantity in descending order.

Myntra · Engagement Analytics — practice this real-world SQL scenario live in your browser.

📖 Story Myntra · Engagement Analytics

Monday morning at Myntra HQ. The Brand Partnerships team is preparing the 'Top Picks' collection for the upcoming fashion sale. Instead of comparing every product across the marketplace, they want to compare each product only with others from the same brand. Products performing better than their brand average will receive additional promotion on the homepage.

🎯 Your Mission

📋 Table Structure

🗂 products

product_id INTEGER 101

product_name TEXT Air Zoom

brand TEXT Nike

category TEXT Shoes

🗂 sales

sale_id INTEGER 1001

product_id INTEGER 101

quantity INTEGER 45

sale_date TEXT 2024-05-01

⚡ Step-by-Step Walkthrough

Calculate the total sales quantity for each product

query.sql

SELECT p.product_id,
       p.product_name,
       p.brand,
       SUM(s.quantity) AS total_sales
FROM products p
JOIN sales s
ON p.product_id = s.product_id
GROUP BY p.product_id,
         p.product_name,
         p.brand
ORDER BY p.brand,
         total_sales DESC;

💡 Explanation

The products table stores product details such as the product name and brand, while the sales table stores every sales transaction. To calculate total sales for a product, we first need to combine these two tables.
JOIN connects every sales transaction with its corresponding product. Without the JOIN, we would know the quantity sold, but we wouldn't know which product or brand it belongs to.
SUM(s.quantity) adds the quantity from every sales transaction of the same product. This gives us the total number of units sold for each product.
GROUP BY combines multiple sales transactions into a single row for every product. Without GROUP BY, SQL cannot calculate a separate total for each product.
At the end of this step, every product has one row with its total sales quantity. We are not comparing products yet. We are simply preparing the base metric that will be used in the next step.
Run this query and verify that products like Air Zoom have higher total sales than other products within the same brand. These totals will be compared against the brand average in the next step.

Calculate the average total sales for products from the same brand

query.sql

SELECT p.product_id,
       p.product_name,
       p.brand,
       SUM(s.quantity) AS total_sales,
       (
           SELECT AVG(product_sales)
           FROM (
               SELECT SUM(s2.quantity) AS product_sales
               FROM products p2
               JOIN sales s2
               ON p2.product_id = s2.product_id
               WHERE p2.brand = p.brand
               GROUP BY p2.product_id
           ) brand_sales
       ) AS brand_avg_sales
FROM products p
JOIN sales s
ON p.product_id = s.product_id
GROUP BY p.product_id,
         p.product_name,
         p.brand
ORDER BY p.brand,
         total_sales DESC;

💡 Explanation

From the previous step, we already know the total sales of every product. Now we need another value to compare against: the average total sales of products belonging to the same brand.
The correlated subquery calculates this average. It runs once for every row returned by the outer query. As the outer query moves from one product to another, the value of p.brand changes, and the inner query automatically recalculates the average for that brand.
Inside the subquery, we first calculate the total sales for every product within the current brand using SUM() and GROUP BY. Once we have the total sales of all products in that brand, AVG() calculates the brand's average sales.
Notice the condition WHERE p2.brand = p.brand. Here, p2.brand belongs to the inner query, while p.brand comes from the outer query. This reference to the outer query is what makes it a correlated subquery.
We are not filtering any products yet. The purpose of this step is simply to display two values for every product: its own total sales and its brand's average sales. These two values will be compared in the final step.

Filter products whose total sales exceed their brand's average sales

query.sql

SELECT p.product_name,
       p.brand,
       SUM(s.quantity) AS total_sales
FROM products p
JOIN sales s
ON p.product_id = s.product_id
GROUP BY p.product_id,
         p.product_name,
         p.brand
HAVING SUM(s.quantity) > (
    SELECT AVG(product_sales)
    FROM (
        SELECT SUM(s2.quantity) AS product_sales
        FROM products p2
        JOIN sales s2
        ON p2.product_id = s2.product_id
        WHERE p2.brand = p.brand
        GROUP BY p2.product_id
    ) brand_sales
)
ORDER BY p.brand,
         total_sales DESC;

💡 Explanation

In the previous step, every product had two important values: its own total sales and the average sales of products from the same brand. The final task is simply to compare these two values.
HAVING is used because total_sales is calculated using the SUM() aggregate function. Since SQL evaluates WHERE before GROUP BY, aggregated values are not available inside the WHERE clause. HAVING filters rows after aggregation is complete.
For every product, SQL first calculates its total sales using SUM(s.quantity). It then executes the correlated subquery to calculate the average sales of all products belonging to the same brand.
If a product's total sales are greater than its brand's average sales, the product is included in the final result. Products with sales equal to or below the average are excluded.
ORDER BY p.brand, total_sales DESC organizes the output by brand and lists the best-performing products first within each brand.
Business Insight: These products are outperforming other products from the same brand. They are strong candidates for the 'Top Picks' collection, homepage promotions, or marketing campaigns because they consistently generate higher sales than their peers.

▶ Practice this live ← Browse all questions