What happens if you using where instead of having to filter aggregated results?

Query returns error or incorrect results because WHERE filters rows before aggregation Fix: Use HAVING clause to filter groups after aggregation

What happens if you not including all non-aggregated columns in group by?

SQL error or unpredictable results due to ambiguous grouping Fix: Include all selected non-aggregated columns in GROUP BY clause

What happens if you confusing rank() and dense_rank() window functions?

Ranks may skip numbers or not, leading to incorrect interpretation of ranking Fix: Choose RANK() if gaps in ranking are acceptable, DENSE_RANK() for consecutive ranks

What happens if you forgetting to partition by category in window functions?

Ranking is done over entire dataset instead of per category, producing wrong results Fix: Use PARTITION BY category in window function to rank within groups

Interview Prepdatabase-management-systemsmediumAmazonGoogleFlipkartPhonePeZepto

Aggregate Functions - GROUP BY, HAVING, Window Functions

Choose your preparation mode3 modes available

Solution Trace

🎯

mediumDBMSAmazonGoogleFlipkart

Imagine you are analyzing sales data for an e-commerce platform and need to summarize total sales per category, filter categories with high sales, and rank products within categories by revenue.

💡 This problem focuses on SQL aggregate functions and window functions, which are essential for summarizing and analyzing grouped data. Beginners often struggle because they confuse WHERE with HAVING, or don't understand how window functions differ from aggregates.

📋

Problem Statement

Given a sales dataset with columns (product_id, category, sales_amount, sale_date), write SQL queries to: 1) Calculate total sales per category using GROUP BY and aggregate functions; 2) Filter categories with total sales above a threshold using HAVING; 3) Rank products within each category by their total sales using window functions such as RANK() or DENSE_RANK(). Output the results accordingly.

Number of rows n can be up to 10^5sales_amount is a positive decimal numbercategory and product_id are non-null stringssale_date is a valid date

💡

Example

Input

"Table sales:\nproduct_id | category | sales_amount | sale_date\nP1 | Electronics | 100 | 2023-01-01\nP2 | Electronics | 150 | 2023-01-02\nP3 | Books | 200 | 2023-01-01\nP4 | Books | 50 | 2023-01-03"

Output

Total sales per category:
Electronics | 250
Books | 250
Categories with sales > 200:
Electronics | 250
Books | 250
Ranking products by sales within category:
Category | product_id | total_sales | rank
Books | P3 | 200 | 1
Books | P4 | 50 | 2
Electronics | P2 | 150 | 1
Electronics | P1 | 100 | 2

First, we sum sales_amount grouped by category. Then, we filter categories with total sales above 200 using HAVING. Finally, we rank products within each category by their total sales using RANK() window function.

No sales data (empty table) → returns empty result sets
All categories have total sales below threshold → HAVING filters out all groups
Multiple products with same sales amount → RANK() assigns same rank, DENSE_RANK() assigns consecutive ranks
Single product category → rank is always 1

⚠️

Common Mistakes

Using WHERE instead of HAVING to filter aggregated results

Query returns error or incorrect results because WHERE filters rows before aggregation

✅ Use HAVING clause to filter groups after aggregation

Not including all non-aggregated columns in GROUP BY

SQL error or unpredictable results due to ambiguous grouping

✅ Include all selected non-aggregated columns in GROUP BY clause

Confusing RANK() and DENSE_RANK() window functions

Ranks may skip numbers or not, leading to incorrect interpretation of ranking

✅ Choose RANK() if gaps in ranking are acceptable, DENSE_RANK() for consecutive ranks

Forgetting to partition by category in window functions

Ranking is done over entire dataset instead of per category, producing wrong results

✅ Use PARTITION BY category in window function to rank within groups

Not handling ties properly when ranking products

Ranks assigned incorrectly, affecting top-N queries or business logic

✅ Use appropriate window function and ordering to handle ties as per requirements

🧠

Brute Force (Manual Aggregation and Filtering)

💡 This approach manually simulates aggregation and filtering by iterating over data, which helps beginners understand the underlying mechanics of GROUP BY and HAVING before using SQL syntax.

Intuition

Group rows by category, sum sales amounts per group, then filter groups by total sales threshold. Finally, rank products within each category by sorting their total sales.

Algorithm

Initialize a dictionary to accumulate total sales per category.
Iterate over each sale record, add sales_amount to the corresponding category total.
Filter categories whose total sales exceed the threshold.
For ranking, group sales by category and product, sum sales per product, then sort and assign ranks.

💡 This step-by-step manual approach clarifies how SQL aggregates and filters data internally, which is often abstracted away in SQL queries.

</>

Code

from collections import defaultdict

def aggregate_and_rank(sales, threshold):
    # Step 1: Aggregate total sales per category
    category_totals = defaultdict(float)
    for record in sales:
        category_totals[record['category']] += record['sales_amount']

    # Step 2: Filter categories by threshold
    filtered_categories = {cat for cat, total in category_totals.items() if total > threshold}

    # Step 3: Aggregate sales per product within filtered categories
    product_totals = defaultdict(float)
    for record in sales:
        if record['category'] in filtered_categories:
            key = (record['category'], record['product_id'])
            product_totals[key] += record['sales_amount']

    # Step 4: Rank products within each category
    from operator import itemgetter
    result = []
    for category in filtered_categories:
        products = [(prod, total) for (cat, prod), total in product_totals.items() if cat == category]
        products.sort(key=lambda x: x[1], reverse=True)
        rank = 1
        prev_total = None
        for i, (prod, total) in enumerate(products):
            if total != prev_total:
                rank = i + 1
            result.append({'category': category, 'product_id': prod, 'total_sales': total, 'rank': rank})
            prev_total = total
    return category_totals, filtered_categories, result

# Driver code
sales_data = [
    {'product_id': 'P1', 'category': 'Electronics', 'sales_amount': 100, 'sale_date': '2023-01-01'},
    {'product_id': 'P2', 'category': 'Electronics', 'sales_amount': 150, 'sale_date': '2023-01-02'},
    {'product_id': 'P3', 'category': 'Books', 'sales_amount': 200, 'sale_date': '2023-01-01'},
    {'product_id': 'P4', 'category': 'Books', 'sales_amount': 50, 'sale_date': '2023-01-03'}
]
threshold = 200
category_totals, filtered_categories, ranked_products = aggregate_and_rank(sales_data, threshold)
print("Category Totals:", category_totals)
print("Filtered Categories:", filtered_categories)
print("Ranked Products:", ranked_products)

Line Notes

category_totals = defaultdict(float)Initialize dictionary to accumulate sums per category

for record in sales:Iterate over each sale record to aggregate data

filtered_categories = {cat for cat, total in category_totals.items() if total > threshold}Filter categories based on total sales threshold

products.sort(key=lambda x: x[1], reverse=True)Sort products descending by total sales for ranking

import java.util.*;

public class AggregateFunctions {
    public static void main(String[] args) {
        List<Map<String, Object>> sales = Arrays.asList(
            Map.of("product_id", "P1", "category", "Electronics", "sales_amount", 100.0, "sale_date", "2023-01-01"),
            Map.of("product_id", "P2", "category", "Electronics", "sales_amount", 150.0, "sale_date", "2023-01-02"),
            Map.of("product_id", "P3", "category", "Books", "sales_amount", 200.0, "sale_date", "2023-01-01"),
            Map.of("product_id", "P4", "category", "Books", "sales_amount", 50.0, "sale_date", "2023-01-03")
        );
        double threshold = 200.0;
        Map<String, Double> categoryTotals = new HashMap<>();
        for (Map<String, Object> record : sales) {
            String category = (String) record.get("category");
            double amount = (double) record.get("sales_amount");
            categoryTotals.put(category, categoryTotals.getOrDefault(category, 0.0) + amount);
        }
        Set<String> filteredCategories = new HashSet<>();
        for (Map.Entry<String, Double> entry : categoryTotals.entrySet()) {
            if (entry.getValue() > threshold) {
                filteredCategories.add(entry.getKey());
            }
        }
        Map<String, Double> productTotals = new HashMap<>();
        for (Map<String, Object> record : sales) {
            String category = (String) record.get("category");
            if (filteredCategories.contains(category)) {
                String product = (String) record.get("product_id");
                double amount = (double) record.get("sales_amount");
                String key = category + "|" + product;
                productTotals.put(key, productTotals.getOrDefault(key, 0.0) + amount);
            }
        }
        for (String category : filteredCategories) {
            List<Map.Entry<String, Double>> products = new ArrayList<>();
            for (Map.Entry<String, Double> entry : productTotals.entrySet()) {
                if (entry.getKey().startsWith(category + "|")) {
                    products.add(entry);
                }
            }
            products.sort((a, b) -> Double.compare(b.getValue(), a.getValue()));
            int rank = 1;
            Double prevTotal = null;
            for (int i = 0; i < products.size(); i++) {
                double total = products.get(i).getValue();
                if (prevTotal == null || total != prevTotal) {
                    rank = i + 1;
                }
                String product = products.get(i).getKey().split("\\|")[1];
                System.out.println("Category: " + category + ", Product: " + product + ", Total Sales: " + total + ", Rank: " + rank);
                prevTotal = total;
            }
        }
    }
}

Line Notes

Map<String, Double> categoryTotals = new HashMap<>();Create map to accumulate sales per category

for (Map<String, Object> record : sales)Iterate over sales records to aggregate

if (entry.getValue() > threshold)Filter categories exceeding sales threshold

products.sort((a, b) -> Double.compare(b.getValue(), a.getValue()));Sort products descending by sales for ranking

#include <iostream>
#include <vector>
#include <unordered_map>
#include <map>
#include <string>
#include <algorithm>

using namespace std;

struct Sale {
    string product_id;
    string category;
    double sales_amount;
    string sale_date;
};

int main() {
    vector<Sale> sales = {
        {"P1", "Electronics", 100, "2023-01-01"},
        {"P2", "Electronics", 150, "2023-01-02"},
        {"P3", "Books", 200, "2023-01-01"},
        {"P4", "Books", 50, "2023-01-03"}
    };
    double threshold = 200;
    unordered_map<string, double> category_totals;
    for (auto &record : sales) {
        category_totals[record.category] += record.sales_amount;
    }
    vector<string> filtered_categories;
    for (auto &entry : category_totals) {
        if (entry.second > threshold) {
            filtered_categories.push_back(entry.first);
        }
    }
    map<pair<string, string>, double> product_totals;
    for (auto &record : sales) {
        if (find(filtered_categories.begin(), filtered_categories.end(), record.category) != filtered_categories.end()) {
            product_totals[{record.category, record.product_id}] += record.sales_amount;
        }
    }
    for (auto &category : filtered_categories) {
        vector<pair<string, double>> products;
        for (auto &entry : product_totals) {
            if (entry.first.first == category) {
                products.push_back({entry.first.second, entry.second});
            }
        }
        sort(products.begin(), products.end(), [](auto &a, auto &b) { return a.second > b.second; });
        int rank = 1;
        double prev_total = -1;
        for (int i = 0; i < (int)products.size(); i++) {
            if (products[i].second != prev_total) {
                rank = i + 1;
            }
            cout << "Category: " << category << ", Product: " << products[i].first << ", Total Sales: " << products[i].second << ", Rank: " << rank << endl;
            prev_total = products[i].second;
        }
    }
    return 0;
}

Line Notes

unordered_map<string, double> category_totals;Map to accumulate sales per category

for (auto &record : sales)Iterate over sales to sum amounts

if (entry.second > threshold)Filter categories by sales threshold

sort(products.begin(), products.end(), [](auto &a, auto &b) { return a.second > b.second; });Sort products descending for ranking

const sales = [
  { product_id: 'P1', category: 'Electronics', sales_amount: 100, sale_date: '2023-01-01' },
  { product_id: 'P2', category: 'Electronics', sales_amount: 150, sale_date: '2023-01-02' },
  { product_id: 'P3', category: 'Books', sales_amount: 200, sale_date: '2023-01-01' },
  { product_id: 'P4', category: 'Books', sales_amount: 50, sale_date: '2023-01-03' }
];
const threshold = 200;

function aggregateAndRank(sales, threshold) {
  const categoryTotals = {};
  sales.forEach(({ category, sales_amount }) => {
    categoryTotals[category] = (categoryTotals[category] || 0) + sales_amount;
  });
  const filteredCategories = Object.entries(categoryTotals)
    .filter(([_, total]) => total > threshold)
    .map(([cat]) => cat);
  const productTotals = {};
  sales.forEach(({ category, product_id, sales_amount }) => {
    if (filteredCategories.includes(category)) {
      const key = `${category}|${product_id}`;
      productTotals[key] = (productTotals[key] || 0) + sales_amount;
    }
  });
  const result = [];
  filteredCategories.forEach(category => {
    const products = Object.entries(productTotals)
      .filter(([key]) => key.startsWith(category + '|'))
      .map(([key, total]) => [key.split('|')[1], total]);
    products.sort((a, b) => b[1] - a[1]);
    let rank = 1;
    let prevTotal = null;
    products.forEach(([prod, total], i) => {
      if (total !== prevTotal) rank = i + 1;
      result.push({ category, product_id: prod, total_sales: total, rank });
      prevTotal = total;
    });
  });
  return { categoryTotals, filteredCategories, rankedProducts: result };
}

const output = aggregateAndRank(sales, threshold);
console.log('Category Totals:', output.categoryTotals);
console.log('Filtered Categories:', output.filteredCategories);
console.log('Ranked Products:', output.rankedProducts);

Line Notes

const categoryTotals = {};Object to accumulate sales sums per category

sales.forEach(({ category, sales_amount }) => {Iterate over sales to aggregate totals

const filteredCategories = Object.entries(categoryTotals).filter(...).map(...)Filter categories exceeding threshold

products.sort((a, b) => b[1] - a[1]);Sort products descending by sales for ranking

⏱

Complexity

TimeO(n + m log m) where n is number of sales and m is number of products in filtered categories

SpaceO(n) for storing aggregates and intermediate results

We iterate over all sales once for aggregation (O(n)), then sort products per category (m log m).

💡 For 100,000 sales, this means roughly 100,000 iterations plus sorting products, which is feasible but not efficient for very large datasets.

Interview Verdict: Use only to introduce concepts - not practical for large datasets

This approach is too manual and inefficient for real databases but is invaluable for understanding how GROUP BY and HAVING work internally.

Approach	Time	Space	Stack Risk	Reconstruct	Use In Interview
1. Brute Force	`O(n + m log m)`	`O(n)`	No	N/A	Mention only - never code due to inefficiency
2. SQL GROUP BY + HAVING	`O(n)`	`O(k) where k is distinct groups`	No	N/A	Code this approach - standard and efficient
3. Window Functions for Ranking	`O(n log n)`	`O(n)`	No	N/A	Mention and code if ranking required - advanced technique

Practice

(1/5)

1. In which scenario is MVCC most beneficial compared to traditional locking mechanisms in a database system?

easy

A. When strict serializability is required and no stale reads are allowed

B. When transactions are short-lived and mostly write-heavy with minimal reads

C. When many concurrent transactions perform mostly read operations with occasional writes

D. When the database workload consists primarily of batch updates with no concurrent reads

Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard

Intuition

Algorithm

Intuition

Algorithm

Intuition

Algorithm

How to Present

Time Allocation

What the Interviewer Tests

Common Follow-ups

When to Use

Signature Phrases

NOT This Pattern When

Similar Problems

Practice

Solution

Step 1: Understand MVCC's strength in concurrency

Step 2: Analyze write-heavy workloads

Step 3: Analyze batch updates without concurrent reads

Step 4: Analyze strict serializability requirements

Final Answer:

Quick Check:

Solution

Step 1: Compare data storage impact on range queries

Step 2: Understand disk I/O implications

Step 3: Analyze other options

Final Answer:

Quick Check:

Solution

Step 1: Understand explain plan cardinality

Step 2: Analyze options

Final Answer:

Quick Check:

Solution

Step 1: Analyze data structure used for order_customer_ids

Step 2: Identify impact on performance and correctness

Final Answer:

Quick Check:

Solution

Step 1: Understand Armstrong's Axioms nature

Step 2: Analyze NULL impact

Step 3: Evaluate options

Final Answer:

Quick Check:

Ready to prove it?