How to Use Window Functions without SQL

Window functions are the secret sauce behind many analytics tasks like ranking rows, calculating running totals, computing moving averages, and spotting trends over time. Typically, tapping into their power has meant diving into SQL, wrangling with complex syntax, or waiting on someone else to write a query and feeling stuck every time. In this article, we’ll show you how to harness the capabilities of window functions without SQL, by using an intuitive, non-code approach.

What Are Window Functions?

Window functions are a type of calculation used in data analysis to give you context across rows without grouping or summarizing everything into a single result.

Think of them as a way to look at the bigger picture while still keeping the details. They help answer questions like:

• What are our top 10 products this month compared to last month?

• How has weekly sales trended over the past quarter?

• How many new customers are we gaining or losing each month this year?

What makes them powerful is that they let you add context like time, grouping, or order to each row, and still keep your full dataset intact.

SQL VS No-SQL Approach to Applying Window Functions

In SQL, these kinds of insights require advanced functions like ROW_NUMBER(), RANK(), or LAG(), used alongside OVER() clauses. But while powerful, these functions are often hard to write, especially for non-technical users. They involve layered logic, precise syntax, and a deep understanding of how data is being grouped and sorted behind the scenes.

With Chata.ai, users don’t need to write code when working with window functions. They just need to know the question they’re trying to answer. They define how the function behaves across your dataset by choosing the metric, the partition, and the sort order. Then, the result comes in a few seconds.

Let’s look at how this querying and selection-based logic works on some examples and compare it to SQL.

➤ Example 1. Ranking Top Customers by Weekly Sales

A retail chain wants to identify their highest-spending customers each week to optimize engagement strategies and retention efforts — critical for revenue growth, personalized marketing, and timely follow-ups.

The question: Who were our top 5 customers each week in May based on total sales?

How It’s Done with SQL

An analyst writes a SQL query using window functions like RANK() or ROW_NUMBER() to calculate weekly customer rankings. They manually define time partitions (e.g., by week), often using PARTITION BY and ORDER BY clauses to rank within each period. The result is usually output to a dashboard or spreadsheet after testing and debugging.

Typical Workflow Steps

• Connect to the sales database

• Write SQL queries like:

SELECT
  WEEK(sale_date) AS week,
  customer_name,
  SUM(sale_amount) AS total_sales,
  RANK() OVER (
    PARTITION BY WEEK(sale_date)
    ORDER BY SUM(sale_amount) DESC
  ) AS rank
FROM
  sales
WHERE
  sale_date BETWEEN '2025-05-01' AND '2025-05-31'
GROUP BY
  week, customer_name
QUALIFY
  rank <= 5;

• Validate ranking logic and ensure correct grouping by week

• Re-run queries for different time periods or segments as needed

• Business users rely on analysts for updates or changes

────────────────────────────────────────────────────────────────────────

How it Works with Chata.ai

Users (not just analysts) type natural language query:

"Show me the top 5 customers by total sales for each week in May."

They will get a result in a few seconds: top customers grouped by each week.

• Chata.ai instantly generates the ranking table and a visualization: no formulas, no code, no waiting.

• Follow-up queries (e.g., “the average order size for each customer”) are answered in seconds with plain English.

➤ Example 2. % of Total Online Sales by Month 

The eCommerce division wants to understand how each department is contributing to online sales over the course of the year, which is critical for digital channel optimization and identifying strong or lagging categories.

How It’s Done with SQL

An analyst writes SQL queries using aggregate functions combined with window functions like SUM() to calculate percentages within each group.

They use PARTITION BY to isolate totals for each month and department, then divide to find each department’s contribution.

Results are often exported or embedded in BI tools for presentation.

Typical Workflow Steps

• Connect to the sales database

• Write SQL queries like:

SELECT
  DATE_TRUNC('month', sale_date) AS month,
  department,
  SUM(sale_amount) AS total_online_sales
FROM
  sales
WHERE
  sale_channel = 'online'
  AND sale_date >= DATE_TRUNC('year', CURRENT_DATE)
GROUP BY
  month, department
ORDER BY
  month, department;

• Format and test results to ensure accuracy

• Share outputs with stakeholders or embed in reports

• Business users rely on analysts for revisions or custom views

────────────────────────────────────────────────────────────────────────

How it Works with Chata.ai

Users type:

"Total online sales by department by month this year."

Then they:

• Add a new column

• Choose % of Total as the function

• Select Partition By: Month to calculate percentage within each month

The result is a grouped, percentage-based view in seconds, no code required and no overload for analysts.

Key Shifts

• No need to learn SQL or technical BI tools

• Instant answers from natural language questions

• Greater autonomy for category managers, merchandisers, and executives

• Decreased reliance on technical staff; analysts can focus on deeper analytics

ROI and Tangible Benefits of a No-SQL Approach

Making Window Functions Accessible to Everyone

What used to take hours of scripting now takes seconds. With Chata.ai, window functions become accessible through natural language and simple point-and-click tools, so business users can rank, compare, and trend data on their own, without waiting on analysts.

Find more examples in the post: 6 Examples of Sales KPIs Powered by Window Functions