Aggregation, Grouping, Window Functions & Advanced SQL Queries

Core Concepts: Aggregation and Grouping

What is Aggregation?

Aggregation is the process of combining multiple rows into a single summary row using functions like COUNT(), SUM(), AVG(), MIN(), and MAX(). Unlike simple queries that return individual records, aggregation answers questions like:

• “How many orders did Daraz process last month?”
• “What is the average loan amount given by NMB Bank?”

How GROUP BY Works

GROUP BY divides rows into groups based on one or more columns, then applies aggregate functions to each group. For example, grouping sales by product category:

SELECT product_category, SUM(quantity) AS total_sold
FROM sales
GROUP BY product_category;

Visual: Grouping in Action

flowchart TD
    A["Raw Sales Data\n(product_id, quantity, category)"] --> B["GROUP BY category"]
    B --> C["Group 1: Electronics\n(100, 200, 300)"] --> D["SUM(quantity) = 600"]
    B --> E["Group 2: Groceries\n(50, 150, 200)"] --> F["SUM(quantity) = 400"]

HAVING vs. WHERE

• WHERE filters before grouping (applies to individual rows).
• HAVING filters after grouping (applies to aggregated results).

Example:

-- Find categories with total sales > 500
SELECT product_category, SUM(quantity) AS total_sold
FROM sales
GROUP BY product_category
HAVING SUM(quantity) > 500;

Comparison Table

Window Functions: Beyond Simple Aggregation

Window functions perform calculations across a set of table rows without collapsing them into a single row (unlike GROUP BY). They use OVER() to define a "window" of rows.

Key Window Functions

Real-World Example: eSewa Transaction Ranking

-- Rank users by total transaction amount (descending)
SELECT
    user_id,
    user_name,
    SUM(amount) AS total_spent,
    RANK() OVER (ORDER BY SUM(amount) DESC) AS spending_rank
FROM transactions
GROUP BY user_id, user_name;

Output:

Visual: Window Function Partitioning

flowchart TD
    A["Transactions Table\n(user_id, amount)"] --> B["PARTITION BY user_id"]
    B --> C["Window 1: User 1001\n(100, 200, 300)"] --> D["RANK() OVER (ORDER BY amount DESC)"]
    B --> E["Window 2: User 1002\n(50, 150, 250)"] --> F["RANK() OVER (ORDER BY amount DESC)"]

Advanced Querying Techniques

Subqueries

Subqueries are queries nested inside other queries. They can appear in:

• WHERE clauses (filtering)
• FROM clauses (derived tables)
• SELECT lists (scalar subqueries)

Example: Find Employees Earning More Than Their Manager

SELECT employee_name, salary
FROM employees e1
WHERE salary > (
    SELECT salary
    FROM employees e2
    WHERE e2.employee_id = e1.manager_id
);

Common Table Expressions (CTEs)

CTEs (with WITH clause) improve readability by breaking complex queries into named temporary result sets.

Example: Recursive CTE for Organizational Hierarchy

WITH RECURSIVE org_hierarchy AS (
    -- Base case: CEO
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees
    WHERE job_title = 'CEO'

    UNION ALL

    -- Recursive case: subordinates
    SELECT e.employee_id, e.name, e.manager_id, oh.level + 1
    FROM employees e
    JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT * FROM org_hierarchy ORDER BY level;

Visual: CTE Flow

Practical Example: Daraz Order Analytics

Scenario: Daraz wants to analyze order data to:

1. Find the top 3 products by sales volume.
2. Identify regions with declining sales.

Solution:

-- 1. Top 3 products by sales volume
SELECT
    product_id,
    product_name,
    SUM(quantity) AS total_sold,
    RANK() OVER (ORDER BY SUM(quantity) DESC) AS sales_rank
FROM orders
GROUP BY product_id, product_name
HAVING SUM(quantity) > 0
ORDER BY sales_rank
LIMIT 3;

-- 2. Regions with declining sales (YoY comparison)
WITH yearly_sales AS (
    SELECT
        region,
        EXTRACT(YEAR FROM order_date) AS year,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY region, EXTRACT(YEAR FROM order_date)
)
SELECT
    region,
    year,
    total_amount,
    LAG(total_amount, 1) OVER (PARTITION BY region ORDER BY year) AS prev_year_amount,
    (total_amount - LAG(total_amount, 1) OVER (PARTITION BY region ORDER BY year)) /
        LAG(total_amount, 1) OVER (PARTITION BY region ORDER BY year) * 100 AS pct_change
FROM yearly_sales
WHERE year > 2022;

Output Interpretation:

• Top Products: Ranked by RANK() for marketing focus.
• Declining Regions: Negative % pct_change highlights areas needing promotions.

In the Real World

1. eSewa Transaction Reports

   • Idea Used: Aggregation (GROUP BY user_id) and window functions (RANK()).
   • How: eSewa’s backend generates monthly reports ranking users by transaction volume to identify high-value customers for targeted offers. The RANK() function helps categorize users into tiers (e.g., Platinum, Gold) for loyalty programs.

2. NMB Bank Loan Portfolio Analysis

   • Idea Used: HAVING with AVG() and COUNT().
   • How: Bank analysts use queries like:

     SELECT branch_id, AVG(loan_amount) AS avg_loan
     FROM loans
     GROUP BY branch_id
     HAVING AVG(loan_amount) > 500000 AND COUNT(*) > 100;
     

     to identify branches with high average loan amounts (potential risk or opportunity).

3. Pathao Driver Performance Dashboard

   • Idea Used: Window functions (LEAD()/LAG()) and CTEs.
   • How: Pathao tracks driver efficiency by comparing consecutive trip times:

     WITH driver_trips AS (
         SELECT
             driver_id,
             trip_id,
             trip_duration,
             LAG(trip_duration, 1) OVER (PARTITION BY driver_id ORDER BY trip_id) AS prev_trip_duration
         FROM trips
     )
     SELECT
         driver_id,
         AVG(trip_duration) AS avg_duration,
         AVG(trip_duration - prev_trip_duration) AS avg_time_change
     FROM driver_trips
     GROUP BY driver_id;
     

     Drivers with consistent low durations are rewarded.

Exam Tip

What Examiners Look For

1. Correct Syntax:

   • Use GROUP BY with aggregate functions (e.g., SUM(), AVG()).
   • Place HAVING after GROUP BY, not WHERE.
   • For window functions, always include OVER() with PARTITION BY/ORDER BY.

2. Logical Flow:

   • Start with simple aggregation, then progress to window functions and CTEs.
   • Label output columns clearly (e.g., AS total_sales).

3. Real-World Application:

   • Expect questions like: “Write a query to find the top 5 products by sales in Kathmandu for Q2 2023.” “Use a window function to rank employees by salary within each department.”
   • Pro Tip: Always include a WHERE clause to filter data before aggregation (e.g., WHERE region = 'Kathmandu').

4. Common Pitfalls:

   • Forgetting to include non-aggregated columns in GROUP BY.
   • Misusing DISTINCT with aggregation (e.g., COUNT(DISTINCT product_id) vs. COUNT(product_id)).
   • Omitting ORDER BY in window functions (leads to undefined behavior).

5. Diagram Expectations:

   • For CTEs or recursive queries, draw a flowchart showing the base case and recursive steps.
   • For window functions, sketch a table with PARTITION BY and ORDER BY lines.

Sample Exam Question Breakdown: Question: “Write a SQL query to find the average salary of employees in each department, excluding departments with fewer than 5 employees. Rank the departments by average salary in descending order.”

Expected Answer Structure:

-- Step 1: Filter departments with >=5 employees
-- Step 2: Group by department and calculate AVG(salary)
-- Step 3: Use window function to rank departments
WITH dept_stats AS (
    SELECT
        department_id,
        department_name,
        AVG(salary) AS avg_salary,
        COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id, department_name
    HAVING COUNT(*) >= 5
)
SELECT
    department_id,
    department_name,
    avg_salary,
    RANK() OVER (ORDER BY avg_salary DESC) AS salary_rank
FROM dept_stats
ORDER BY salary_rank;

Marks Distribution:

• Correct GROUP BY and HAVING: 3 marks
• Proper window function usage: 3 marks
• Logical ordering and clarity: 2 marks
• Real-world relevance (e.g., excluding small departments): 2 marks