SQL-Focused BI Exercises: Window Functions, Ranking, and Aggregates

• 13.09.2025
• Blog
• Ethan Martinez
• 0

When it comes to business intelligence (BI), SQL is a superpower that transforms raw data into actionable insights. Whether you’re a data analyst, a BI developer, or a SQL enthusiast, mastering the more nuanced aspects of SQL—particularly window functions, ranking functions, and aggregates—is essential for uncovering deeper patterns and trends. These advanced SQL topics not only allow for more efficient queries but also enable dashboards and reports that truly tell a story. Let’s explore how these powerful components can enhance your BI toolkit in meaningful ways.

Understanding Window Functions

Window functions allow you to perform calculations across a set of table rows that are somehow related to the current row. They differ from aggregate functions because they don’t collapse rows into a single output — instead, they maintain the individual rows while enriching them with additional data.

Some of the most commonly-used window functions include:

• ROW_NUMBER(): Assigns a unique sequential integer to rows within a partition.
• RANK(): Similar to ROW_NUMBER but assigns the same rank to rows with identical values.
• NTILE(n): Divides ordered rows into a specified number of roughly equal parts.
• LAG() and LEAD(): Access data from previous or subsequent rows without using a self-join.
• SUM(), AVG(), MIN(), MAX() used as window functions with OVER() for running totals or moving averages.

These functions are invaluable when you want to calculate trends, create leaderboards, or perform time-based analysis without using subqueries or complex self-joins.

Ranking Functions in BI Reports

Ranking data is fundamental to business scenarios like identifying top-performing products, sales representatives, or marketing campaigns. Let’s look at a practical example involving the RANK() and DENSE_RANK() functions:

SELECT 
  employee_id,
  department,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;

This query returns employees’ ranks within their respective departments based on salary. The PARTITION BY clause ensures that the ranking restarts for each department, which is often required in BI use cases such as departmental comparisons or segment-based analysis.

When to Use Different Ranking Functions

• ROW_NUMBER(): Best when you need a unique, sequential number even if duplicate values exist.
• RANK(): Use when you want duplicates to share the same rank, but allow for gaps between them.
• DENSE_RANK(): Similar to RANK() but does not skip ranking values when duplicates occur.

Choosing the right ranking function depends on how you want to reflect ties in your BI tools. For instance, leaderboards in sales dashboards often use DENSE_RANK() to handle tie scores without creating gaps in the ranking.

Using Aggregates with Windowing for Greater Insights

Aggregations are foundational to BI, but window-based aggregates elevate your analysis by allowing context-specific calculations without sacrificing row-level granularity. For example, calculating average sales per region or cumulative revenue doesn’t require rolling up your data completely.

Example: Calculating running totals for revenue per region

SELECT
  region,
  sales_date,
  revenue,
  SUM(revenue) OVER (PARTITION BY region ORDER BY sales_date) AS running_total
FROM regional_sales;

This query allows analysts to view how revenue accumulates over time for each region—a valuable feature for trend analysis and forecasting within BI platforms.

BI Use Cases That Shine with These Techniques

Understanding these SQL capabilities unlocks countless possibilities for crafting compelling insights in business intelligence reports. Here are a few practical examples:

1. Sales Performance Trends

Using LEAD() and LAG(), analysts can calculate period-over-period differences in sales, helping identify surges or drops in performance.

SELECT
  product_id,
  sales_month,
  revenue,
  LAG(revenue) OVER (PARTITION BY product_id ORDER BY sales_month) AS prev_month_revenue,
  revenue - LAG(revenue) OVER (PARTITION BY product_id ORDER BY sales_month) AS revenue_change
FROM product_sales;

2. Customer Retention Analysis

With ROW_NUMBER() and DATEDIFF(), you can calculate the time between orders or flag first-time customers—critical for assessing customer loyalty and lifetime value (CLV).

3. Contribution to Total Metrics

By using window-based aggregates, it’s easy to calculate each row’s percentage contribution to broader totals. For example:

SELECT
  department,
  employee_id,
  salary,
  SUM(salary) OVER (PARTITION BY department) AS dept_total,
  ROUND((salary * 1.0 / SUM(salary) OVER (PARTITION BY department)) * 100, 2) AS pct_of_total
FROM employees;

This can power visualizations like donut charts or waterfall graphs that help stakeholders understand proportional contributions by segment.

Tips for Practicing SQL-Focused BI Exercises

To master these techniques, hands-on practice is key. Here’s how you can structure effective SQL exercises focused on BI analytics:

Start with Structured Datasets

Use common BI datasets like e-commerce sales, HR employee tables, or marketing campaign results. These datasets can help mimic real-world scenarios and make practice more relevant.

Select a Business Goal

• Identify the top 5 products contributing to 80% of revenue.
• Measure regional performance against the overall average.
• Build a leaderboard for sales representatives ranked by quarter.

Layer Techniques

Try combining ranking and window aggregates in a single query. For instance, calculate each product’s rank and cumulative revenue percentage to identify what’s driving most sales (Pareto analysis).

Build Reports Around Insights

Once you have powerful queries, embed them in dashboards using tools like Power BI, Tableau, or Looker. The combination of SQL logic and visual analytics enables compelling storytelling through data.

Common Pitfalls and How to Avoid Them

As powerful as window functions and ranking are, they can be misused. Be mindful of these common errors:

• Incorrect Partitioning: Always double-check your PARTITION BY clause. Getting it wrong may aggregate values across unintended groups.
• Order of Execution Confusion: Understand that WHERE filters are applied before window functions are computed. Use QUALIFY or CTEs to filter on window-function results effectively.
• Using Aggregates in SELECT and WHERE Together: If you’re mixing normal aggregate functions and window functions, be cautious of grouping scopes. Each function serves a different level of summarization.

Conclusion

Mastering SQL window functions, ranking techniques, and aggregates is a critical step in becoming an advanced BI practitioner. They empower you to create detailed, dynamic, and insightful reports that move beyond static summaries to analytics that reflect the flow and patterns in your data. Whether you’re diving into cohort analysis, performance metrics, or predictive trend spotting, these SQL features form the backbone of robust data-driven decision-making. Start experimenting with them today and take your BI skills to the next level!