Aggregation & Advanced SQL Querying: GROUP BY, HAVING, Subqueries, Joins, Views, CTEs

TAKEAWAYS:

• Aggregation functions (SUM, AVG, COUNT, GROUP BY, HAVING) summarize data and filter grouped results.
• Subqueries and correlated subqueries allow nested queries to solve complex problems in a single query.
• Joins (INNER, LEFT, RIGHT, FULL) combine data from multiple tables based on related keys.
• Views simplify complex queries and improve security by exposing only relevant data.
• Common Table Expressions (CTEs) improve readability and modularity in complex queries.
• Window functions (OVER, PARTITION BY, RANK) enable advanced analytics without collapsing rows.

1. Aggregation Functions: Summarizing Data

Aggregation functions reduce rows to a single value, enabling analysis of large datasets. Common functions include:

Key Concepts

• GROUP BY divides data into groups based on one or more columns.
• HAVING filters groups after aggregation (unlike WHERE, which filters rows before aggregation).

Example: Employee Salary Analysis

Consider a employees table:

employees (EmpID, FirstName, LastName, Salary, DeptID)

Query: Find the average salary per department where the average salary is above 50,000.

SELECT DeptID, AVG(Salary) AS AvgSalary
FROM employees
GROUP BY DeptID
HAVING AVG(Salary) > 50000;

Output:

Visualization: Grouped Salary Data

This table shows how GROUP BY and HAVING filter departments by average salary.

2. Subqueries: Nested Queries for Complex Logic

Subqueries allow queries to be embedded within other queries. Types include:

• Scalar subqueries (return a single value).
• Correlated subqueries (depend on outer query rows).
• Inline views (subqueries used as tables).

Example: Find Employees Earning More Than Their Department’s Average

SELECT FirstName, LastName, Salary
FROM employees e1
WHERE Salary > (
    SELECT AVG(Salary)
    FROM employees e2
    WHERE e2.DeptID = e1.DeptID
);

Explanation:

• The inner subquery calculates the average salary for each department.
• The outer query compares each employee’s salary to their department’s average.

Visualization: Correlated Subquery Flow

sequenceDiagram
    actor User
    participant OuterQuery
    participant InnerQuery
    User->>OuterQuery: Fetch employees
    loop For each employee
        OuterQuery->>InnerQuery: Calculate avg salary for DeptID = e1.DeptID
        InnerQuery-->>OuterQuery: Return avg salary
        OuterQuery->>User: Return employees with Salary > avg
    end

3. Joins: Combining Tables

Joins merge data from multiple tables based on related keys. Common types:

Example: Employee-Department Join

SELECT e.FirstName, e.LastName, d.DeptName
FROM employees e
INNER JOIN departments d ON e.DeptID = d.DeptID;

Output:

Visualization: INNER JOIN vs LEFT JOIN

• INNER JOIN returns only rows where DeptID matches in both tables.
• LEFT JOIN returns all employees, even if their department doesn’t exist.

4. Views: Virtual Tables for Simplified Queries

Views are stored SQL queries that act as virtual tables. They:

• Simplify complex queries.
• Improve security by restricting access to specific data.
• Save time by reusing query logic.

Example: Create a View for High-Earning Employees

CREATE VIEW HighEarners AS
SELECT FirstName, LastName, Salary
FROM employees
WHERE Salary > 100000;

Query the View:

SELECT * FROM HighEarners;

Real-World Use: Daraz Order Analytics

Daraz uses views to track high-value orders:

CREATE VIEW PremiumOrders AS
SELECT order_id, customer_id, total_amount
FROM orders
WHERE total_amount > 5000;

This view helps Daraz identify VIP customers for promotions.

5. Common Table Expressions (CTEs): Modular Queries

CTEs (also called "WITH" clauses) break complex queries into temporary result sets. Syntax:

WITH CTE_Name AS (
    SELECT ... -- Subquery
)
SELECT * FROM CTE_Name;

Example: Employee Turnover Analysis

WITH Departments AS (
    SELECT DeptID, COUNT(*) AS EmployeeCount
    FROM employees
    GROUP BY DeptID
),
HighTurnover AS (
    SELECT d.DeptID, d.DeptName, e.EmployeeCount
    FROM Departments d
    JOIN (
        SELECT DeptID, COUNT(*) AS EmployeeCount
        FROM employees
        WHERE HireDate < '2020-01-01'
        GROUP BY DeptID
    ) e ON d.DeptID = e.DeptID
    WHERE e.EmployeeCount < 5
)
SELECT * FROM HighTurnover;

Output:

Visualization: CTE Workflow

6. Window Functions: Advanced Analytics Without Collapsing Rows

Window functions perform calculations across a set of table rows without grouping (unlike GROUP BY). Common functions:

• OVER(): Defines the window.
• PARTITION BY: Divides data into partitions.
• RANK(), DENSE_RANK(), ROW_NUMBER(): Assigns ranks.

Example: Rank Employees by Salary in Each Department

SELECT
    FirstName,
    LastName,
    Salary,
    RANK() OVER (PARTITION BY DeptID ORDER BY Salary DESC) AS SalaryRank
FROM employees;

Output:

Real-World Use: NEPSE Stock Rankings

NEPSE uses window functions to rank stocks by market capitalization:

SELECT
    StockName,
    MarketCap,
    RANK() OVER (ORDER BY MarketCap DESC) AS MarketRank
FROM stocks;

This helps investors identify top-performing stocks.

In the Real World

1. eSewa: Transaction Aggregation

   • eSewa uses aggregation to calculate daily transaction volumes per merchant.
   • Query: SELECT MerchantID, COUNT(*) AS Transactions FROM transactions GROUP BY MerchantID HAVING COUNT(*) > 1000;
   • This helps eSewa identify high-volume merchants for rewards.

2. Pathao: Ride Demand Analysis

   • Pathao uses window functions to rank drivers by ride completion rate.
   • Query: SELECT DriverID, RANK() OVER (ORDER BY RidesCompleted DESC) AS DriverRank FROM drivers;
   • This ensures top drivers get priority dispatch.

3. Ncell: Customer Churn Prediction

   • Ncell uses correlated subqueries to identify customers likely to churn (e.g., those with low usage and high complaints).
   • Query:

     SELECT CustomerID
     FROM customers c
     WHERE UsageMonths < 3
     AND (
         SELECT COUNT(*)
         FROM complaints
         WHERE CustomerID = c.CustomerID
     ) > 5;
     

Exam Tip

• Focus on aggregation: GROUP BY, HAVING, and window functions are high-weight topics. Practice combining them (e.g., GROUP BY + HAVING + ORDER BY).
• Master joins: Tu examiners love testing INNER, LEFT, and FULL JOIN scenarios. Draw table snapshots to visualize joins.
• Subqueries vs CTEs: Know when to use each. Subqueries are for simple logic; CTEs for complex, multi-step queries.
• Views: Always mention security and query simplification as advantages.
• Window functions: These are newer but appear in advanced papers. Practice RANK(), PARTITION BY, and OVER().
• Real-world mapping: Relate queries to Nepali companies (e.g., Daraz orders, Ncell data). Use the examples above as templates.

Practice Question: Given the employees and departments tables, write a query to:

1. Find the department with the highest average salary.
2. List employees in that department who earn more than the company-wide average salary.

(Hint: Use a subquery for the department and another for the company average.)