Blog Articles
Blog Articles
Blog Articles
6 Examples of Sales KPIs Powered by Window Functions
Jun 18, 2025
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.
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: It doesn't just measure individual product sales amounts, but each product’s position in overall performance week over week.
Why it requires a window function: It compares 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:
Interpretation: Sales team 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: It doesn'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: It calculates 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:
Enter a query: Total promotion cost by media type per month in Q1.
Add a New Column and choose % of Total as the function
Select the Total Promotion Cost column and Partition By: Month to calculate percentage within each month.
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: It doesn't just track how much was spent on shipping each day, but identifies consistent trends in fulfillment cost efficiency, which helps reduce waste, negotiate better carrier rates, and protect margins.
Why it requires a window function: It applies 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: Sales team 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: It doesn't just look at daily or monthly totals; it tracks 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: It calculates 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:
Run a query: Total sales by month this year to current month
Add a New Column → Select Cumulative Sum and apply it to the Total Sales column
Select Order By: Month
Interpretation: Sales team sees 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.
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: It doesn't just show how much each region contributed individually, it reveals how total sales build up across regions, allowing salespeople 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:
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.
Select the Total Sales column and Partition By: None to calculate each region’s contribution out of the total.
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: It doesn't only shows how many orders were placed; it identifies 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:
Query: Number of orders by month this year
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.
ROI: Example-Driven Business Outcomes
KPI Example | Traditional Method | Chata.ai Workflow | User/Business ROI |
|---|---|---|---|
Sales Rep Ranking | Manual query and Excel rank | Type “show top reps by sales by week in April” | Save hours generating lists; faster reward and intervention actions |
% of Total Contribution | Custom formula in reports | Type “Total promotion cost by media type per month in Q1.” + choose % of Total and Partition by | Non-analysts can answer ad hoc; improved agility in pricing/promo decisions |
Moving Average | Spreadsheet calculations | Type “number of store purchase orders by day over the last 30 days” + choose Moving Average and Order by | Time savings; better forecast accuracy with up-to-date trends |
Cumulative Sum and % | Multiple pivot tables | Type “Total sales by month this year” + choose Cumulative Sum and Order by | Real-time tracking of goal progress; enhanced team alignment |
Change Over Time | Manual period comparisons | Type “Number of orders by month this year” + choose Percent Change and Order by | Transparent trend monitoring; identify issues before they escalate |
Quantifiable Benefits
Time to Insight: Reduces reporting time from days or hours to minutes or seconds.
Resource Savings: Fewer analyst hours spent on routine tasks; teams of any size can self-serve data needs.
Error Reduction: Automated, template-based outputs lower costly mistakes compared to manual Excel work.
Decision Quality: Leaders respond to market changes faster with real-time, trustworthy data at their fingertips.
This no-code approach makes advanced analytics accessible to any business user. Compare how window functions work with SQL VS Non-SQL workflows.
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. Users don’t need to write complex formulas to get those insights.
Put Window Functions to Work in Seconds with Chata.ai → Book a demo
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.
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: It doesn't just measure individual product sales amounts, but each product’s position in overall performance week over week.
Why it requires a window function: It compares 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:
Interpretation: Sales team 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: It doesn'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: It calculates 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:
Enter a query: Total promotion cost by media type per month in Q1.
Add a New Column and choose % of Total as the function
Select the Total Promotion Cost column and Partition By: Month to calculate percentage within each month.
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: It doesn't just track how much was spent on shipping each day, but identifies consistent trends in fulfillment cost efficiency, which helps reduce waste, negotiate better carrier rates, and protect margins.
Why it requires a window function: It applies 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: Sales team 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: It doesn't just look at daily or monthly totals; it tracks 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: It calculates 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:
Run a query: Total sales by month this year to current month
Add a New Column → Select Cumulative Sum and apply it to the Total Sales column
Select Order By: Month
Interpretation: Sales team sees 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.
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: It doesn't just show how much each region contributed individually, it reveals how total sales build up across regions, allowing salespeople 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:
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.
Select the Total Sales column and Partition By: None to calculate each region’s contribution out of the total.
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: It doesn't only shows how many orders were placed; it identifies 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:
Query: Number of orders by month this year
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.
ROI: Example-Driven Business Outcomes
KPI Example | Traditional Method | Chata.ai Workflow | User/Business ROI |
|---|---|---|---|
Sales Rep Ranking | Manual query and Excel rank | Type “show top reps by sales by week in April” | Save hours generating lists; faster reward and intervention actions |
% of Total Contribution | Custom formula in reports | Type “Total promotion cost by media type per month in Q1.” + choose % of Total and Partition by | Non-analysts can answer ad hoc; improved agility in pricing/promo decisions |
Moving Average | Spreadsheet calculations | Type “number of store purchase orders by day over the last 30 days” + choose Moving Average and Order by | Time savings; better forecast accuracy with up-to-date trends |
Cumulative Sum and % | Multiple pivot tables | Type “Total sales by month this year” + choose Cumulative Sum and Order by | Real-time tracking of goal progress; enhanced team alignment |
Change Over Time | Manual period comparisons | Type “Number of orders by month this year” + choose Percent Change and Order by | Transparent trend monitoring; identify issues before they escalate |
Quantifiable Benefits
Time to Insight: Reduces reporting time from days or hours to minutes or seconds.
Resource Savings: Fewer analyst hours spent on routine tasks; teams of any size can self-serve data needs.
Error Reduction: Automated, template-based outputs lower costly mistakes compared to manual Excel work.
Decision Quality: Leaders respond to market changes faster with real-time, trustworthy data at their fingertips.
This no-code approach makes advanced analytics accessible to any business user. Compare how window functions work with SQL VS Non-SQL workflows.
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. Users don’t need to write complex formulas to get those insights.
Put Window Functions to Work in Seconds with Chata.ai → Book a demo
More Updates
Implement the power of self-service analytics
with an easy-to-use conversational messenger
Meet Team Chata.ai
