Advanced Grouping, Window Functions, Ranking, and Reporting Queries - Textnotes

Advanced Grouping, Window Functions, Ranking, and Reporting Queries

Master SQL for analytics with this comprehensive guide. Learn advanced grouping (GROUPING SETS, ROLLUP, CUBE), ranking and window functions, date and string operations, and reporting queries. Practice sales analysis, running totals, and moving averages for real-world analytics.

1. Introduction

SQL analytics allows you to extract insights from data efficiently.

  1. Advanced SQL techniques help generate aggregated summaries, rankings, and running totals.
  2. Analytics queries are essential for business intelligence, reporting, and dashboards.

Key Points:

  1. GROUPING SETS, ROLLUP, and CUBE provide flexible aggregations.
  2. Window functions enable ranking and calculations over partitions.
  3. Date and string functions allow time-series analysis and data transformations.

2. Advanced Grouping

2.1 GROUPING SETS

  1. Perform multiple grouping operations in a single query.

SELECT Region, Quarter, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY GROUPING SETS ((Region, Quarter), (Region), (Quarter), ());

2.2 ROLLUP

  1. Computes hierarchical totals.

SELECT Region, Quarter, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY ROLLUP(Region, Quarter);

2.3 CUBE

  1. Computes all possible combinations of aggregates.

SELECT Region, Quarter, Product, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY CUBE(Region, Quarter, Product);

3. Ranking and Window Functions

  1. Window functions calculate values over partitions of rows without collapsing results.

Common Functions

  1. ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()

Example – Rank Employees by Salary per Department:


SELECT Name, Department, Salary,
RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS DeptRank
FROM Employees;

Running Total Example:


SELECT Name, Salary,
SUM(Salary) OVER (ORDER BY Name) AS RunningTotal
FROM Employees;

Moving Average Example:


SELECT SaleDate, Amount,
AVG(Amount) OVER (ORDER BY SaleDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg
FROM Sales;

4. Date and String Functions

  1. Date functions: DATEADD(), DATEDIFF(), YEAR(), MONTH()
  2. String functions: SUBSTRING(), CONCAT(), TRIM(), UPPER(), LOWER()

Examples:


-- Calculate year-over-year sales
SELECT YEAR(SaleDate) AS SaleYear, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY YEAR(SaleDate);

-- Format customer name
SELECT CONCAT(UPPER(FirstName), ' ', UPPER(LastName)) AS FullName
FROM Customers;

5. Reporting Queries

  1. Combine grouping, ranking, and window functions for analytics reports.
  2. Examples: Sales by region/quarter, top-performing products, trend analysis.

Example – Sales Analysis by Region and Quarter:


SELECT Region, Quarter, SUM(Sales) AS TotalSales,
RANK() OVER (PARTITION BY Quarter ORDER BY SUM(Sales) DESC) AS QuarterRank
FROM Sales
GROUP BY Region, Quarter;

6. Practical Exercises

  1. Analyze sales by region and quarter using ROLLUP and CUBE.
  2. Rank employees by salary in each department using window functions.
  3. Compute running totals of sales month-wise.
  4. Calculate 3-month moving average for sales data.
  5. Format customer names and extract year/month from order dates.

7. Tips for Beginners

  1. Always use PARTITION BY for window functions to scope calculations.
  2. GROUPING SETS, ROLLUP, and CUBE reduce multiple queries for aggregations.
  3. Use date and string functions to transform raw data for reporting.
  4. Test analytics queries on sample data before applying to large datasets.
  5. Combine window functions with CASE statements for advanced reporting.


Next Step: After mastering SQL for Analytics, the next module is NoSQL & Big Data SQL Concepts, where you’ll learn querying JSON/document data, HiveQL, BigQuery SQL, and Python/R integration for analytics.