6 Examples of Sales KPIs Powered by Window Functions

Window functions are how data tells a story. When you don’t just want to see how much your team sold this month, but also who’s leading, how numbers are trending week over week, or how close you are to hitting your target. Window functions cover some of the most essential KPIs by giving them depth. They let you compare, track, rank, and accumulate within the context of your dataset.  In this article, we will show you the KPIs that window functions power by adding an extra context between the rows and how Chata.ai helps you get them instantly, with no code and no complexity. 

KPIs That Require Window Functions

Window functions are essential for calculating KPIs that depend on comparisons, trends, or aggregations across related rows while preserving row-level detail. Unlike regular aggregation (like SUM() or AVG()), which collapses rows, window functions retain each row and add valuable insight based on context. That way, you can see how things change over time, across groups, or in relation to other values.  

KPIs Examples: 

• Percent of Total: revenue contribution by product, sales by rep as % of team total, % of support tickets by category, marketing spend by channel as % of total, return rate by region as % of total returns. 

• Rank: top 10 best-selling products, highest-performing sales reps by revenue, top-performing campaigns by roi, most returned items, highest revenue-generating customers. 

• Moving Average: 7-day moving average of daily orders, weekly average of inventory levels, rolling average churn rate, monthly average of support tickets, average shipping cost per order over time. 

• Cumulative Sum: year-to-date revenue, running total of orders, total units sold over time, cumulative ad spend, accumulated user signups by week. 

• Cumulative Percent: revenue concentration by product, return concentration by SKU, share of total orders by top customers, cumulative conversion rate by step in funnel, contribution of top regions to total revenue. 

• Change: month-over-month revenue change, daily change in active users, weekly change in support tickets, inventory level change per day, change in customer satisfaction score month to month. 

Below are the detailed KPIs use cases for window functions and instructions on how to easily work with them by using natural language queries. 

1. Rank: Best-selling products 

Use Case: Which products ranked in the top 3 for total online sales each week in the last month? 

Why it’s valuable: You don’t just measure individual product sales amounts, but each product’s position in overall performance week over week. 

Why it requires a window function: You compare product sales against total product sales per week while keeping each row (product + week) visible. This requires a RANK() OVER (PARTITION BY week ORDER BY sales DESC) window function. 

How it works with Chata.ai: 
Enter a query: Top 3 products by total online sales by week for the last month.  Just one simple question, no SQLs or complex data manipulation.  

The result you get:

The result you get in a table: 

Interpretation: You can instantly see top 3 best-sellers week by week Helpful for tracking emerging trends and monitoring sustained performance across weeks (e.g., Brand #220 white bread appears multiple times) 

2. % of Total: Promotion Spend by Media Type 

Use Case: What percent of total promotion spend did each media type receive per month in Q1? 

Why it’s valuable: You don’t just measure how much was spent; you understand how the budget was distributed across channels over time, helping marketing and finance teams adjust strategy, spot overspending, and align spend with results. 

Why it requires a window function: You need to calculate each media type’s share of the monthly total, without filtering out rows or losing the breakdown. This requires a Percent of Total OVER (PARTITION BY month) window function. 

How it works with Chata.ai: 

1. Enter a query: Total promotion cost by media type per month in Q1. 

2. Add a New Column and choose % of Total as the function 

3. Select the Total Promotion Cost column and Partition By: Month to calculate percentage within each month. 

The result you get in a table: 

Interpretation: TV consistently takes the largest share of spend (~26% per month) Online and Magazine receive a much smaller allocation (around 6–11%), which may raise strategic questions about digital emphasis Easily track budget shifts across months and re-balance spend if needed 

3. Moving Average: Shipping Cost per Order 

Use Case: What is the 7-day moving average shipping cost per order per day over the last 30 days? 

Why it’s valuable: You don’t just track how much you spend on shipping each day, you identify consistent trends in fulfillment cost efficiency, which helps reduce waste, negotiate better carrier rates, and protect margins. 

Why it requires a window function: You apply a moving average across a 7-day window to smooth out spikes caused by heavy shipping days or bulk fulfillment. This requires a Moving Average OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) window function. 

How it works with Chata.ai: 

Step 1: 
Run a query: Total shipping cost and number of store purchase orders by day over the last 30 days 

Step 2: 
Add a New Column → Select Custom Formula 
Apply: Shipping Cost per Order = Shipping Cost / Orders 

Step 3: 
Add a New Column → Select Moving Average and apply to Shipping Cost per Order 
Order By: Day 
Precede Interval *: 7 days  

Interpretation: You can spot rising cost trends before they hit profitability. The smooth average gives a more reliable operational benchmark for cost per order over time. 

4. Cumulative Sum: Cumulative Sales 

Use Case: How many sales have been generated so far this year? 

Why it’s valuable: You’re not just looking at daily or monthly totals; you’re tracking how revenue accumulates over time to measure progress toward annual goals, detect seasonality, and identify when key sales milestones were hit. 

Why it requires a window function: You calculate a running total of revenue, day by day or month by month, while still showing each individual row. This requires a Cumulative Sum OVER (ORDER BY date) window function to continuously add revenue values in chronological order. 

How it works with Chata.ai: 

1. Run a query: Total sales by month this year to current month 

2. Add a New Column → Select Cumulative Sum and apply it to the Total Sales column 
   Select Order By: Month  

Interpretation: You see exactly how sales is stacking up over time. Enables goal pacing (e.g., are we on track for $2M this year?) Combine with a target line to visualize progress on dashboards. 

5. Cumulative Percent: Sales Contribution by Region 

Use Case: What percent of total sales is cumulatively contributed by each region during May 2025? 

Why it’s valuable: You don’t just see how much each region contributed individually, you reveal how total sales build up across regions, allowing you to understand geographic concentration, prioritize top-performing markets, and make more informed decisions on where to scale or optimize. 

Why it requires a window function: This use case uses % of Total to calculate each region’s share, and Cumulative Percent to show how those shares add up row by row, revealing the order in which regions collectively reach 100% of sales. 

How it works with Chata.ai: 

1. Enter a query: Total sales by region for May 2025. Optionally, to see how Cumulative Percent works, add a New Column and choose % of Total as the function.

2. Select the Total Sales column and Partition By: None to calculate each region’s contribution out of the total. 

3. Add another New Column and choose Cumulative Percent as the function. Order By: Total Sales descending to see how top regions build toward 100%.  

Interpretation: West and East regions alone contributed over 50% of total sales. By the time the top four regions are included, you’ve reached nearly 80% of total performance. This view is powerful for identifying top-performing territories, regional revenue concentration, and where to focus growth efforts. 

6. Change: Month-over-Month Change 

Use Case: How is the number of store purchase orders changing month over month in 2025? 

Why it’s valuable: You not only see how many orders were placed; you identify how those numbers are shifting from month to month. This reveals trends in store demand, seasonality, or the impact of campaigns and supply chain decisions. 

Why it requires a window function: To calculate month-over-month growth, you need to compare each month’s order count to the previous one, which requires a Percent Change OVER (ORDER BY month) window function. This keeps each row visible while showing how it changed from the previous month. 

How it works with Chata.ai: 

1. Query: Number of orders by month this year 

2. Add a New Column → Select Percent Change. Apply it to the Number of Store Purchase Orders column 
   Order By: Month 

Interpretation: Growth was positive in February and March — indicating increasing demand or purchasing activity. The sharp drop in June could signal reduced store activity, inventory saturation, or seasonal slowdown worth investigating. 

Power Your KPIs with Window Functions  

The most useful KPIs don’t just report numbers; they reveal movement and contribution. Window functions make that possible. They add the context your data needs to answer deeper questions. They show how things change over time, across groups, or in relation to other values. With tools by Chata.ai, it’s all point-and-click that works in seconds. You don’t need to write complex formulas to get those insights. 

Put Window Functions to Work in Seconds with Chata.ai → Book a demo