What are Window functions

What are Window functions and How to use them properly in Low/No-Code? A full guide with Examples

By Brian Laleye · June 28, 2022 · 8 min read

When it comes to data, SQL is the lingua franca.

That’s why it’s essential to know SQL as a business.

What makes SQL so important?

Well, data is the lifeblood of organizations today. It’s what drives everything from customer satisfaction surveys to product recommendations and advertising campaigns.

And because databases store this information, it’s important that organizations have a way of accessing and manipulating it quickly and easily. This is where SQL comes in handy.

In this article, we will focus on a crucial SQL concept called Window functions that is crucial to get the most out of your data.

What is the Window function?

Window functions are a powerful tool that can be used to work with data that is stored over multiple rows and columns.

SQL window functions can be used with table-valued parameters, tables, and derived tables.

The first thing to understand about window functions is that they operate on sets of rows, known as windows.

The rows in each window are ordered from left to right based on the ordering of the OVER clause.

Difference between Group by and Window functions

The Group by and window functions are a powerful set of tools that can be used to answer complex questions about your data.

The Group by function allows you to group together rows by some column (or columns) and then perform calculations on those groups.

You can also use the group by function if you want to perform aggregations on each group, such as counting all of the rows in each group or calculating the average value in each group.

The Window function allows you to apply aggregate calculations across a range of rows and/or columns, including aggregating over groups in different ways.

The difference between these two functions is that the window function has two additional arguments that must be specified: OVER (PARTITION BY) and ORDER BY.

Let’s take an example: Group by vs Window function

Here we have, for a given company, all the orders in $ for a set of customers over a year:

Then choose and fill out the credentials of the selected connector:

Order chart

As you can see with the Group by function, we’ve 3 results returned by the average function and with the Window function we’ve our original 10 rows returned, plus a new one that is aggregating the average amount of orders for each relevant row.

Window function Syntax: Code vs Low/No-code

The basic concepts you need to know are:

  • Over(): returns all values of the specified expression that are associated with the current row.
  • Partition By(): all the selected columns on which you perform the query.
  • Order By(): determines how the rows from each partition (column selected) are ordered within their partition.
  • Window function(): any aggregate, analytical or ranking function.
 

Thus, the basic backbone of a window function is as follows: 

With a classic SQL editor:

Window functions with a classic editor

With RestApp in No/Low Code

Window functions in No Code

Let’s take an example: 

If we take our above example on orders, to get the same results we would need the following query: 

SQL Query on orders

How to use the Window functions?

We know how the window function works as syntax. Let’s dive into the different types of window functions that can be used in place of the orange font below: 

Here are below the 3 types of window functions that you need to know: aggregate, ranking and analytical. In each category, you can see some underlying functions within each type: 

Overview Window functions

Overview of each type of window function: 

  • Aggregate functions: These functions calculate aggregations such as average, sum, count, maximum or minimum values, or standard deviation within each window or partition.
  • Ranking functions: These functions are used for ranking rows within its partition.
  • Analytical (or Value) functions: These functions let us compare values from previous or following rows within the partition.

Window functions: Focus on Window Aggregate

With a low/no-code editor, you need to select the Window Agg operation, in the function field, you can choose in this dropdown list the relevant function:

  • SUM()
  • AVG()
  • MEAN()
  • VAR()
  • STDDEV()
  • MIN()
  • MAX()
  • COUNT()
 

For example, for any of the aggregation functions above, in order to use them, you just need to fill up the “Function” dropdown field:

Windowing aggregate

Window functions: Focus on Window Ranking

Windowing ranking functions

With a low/no-code editor, you need to select the Window Ranking operation, in the function field, you can choose in this dropdown list the relevant function:

  • ROW_NUMBER()
  • RANK()
  • DENSE_RANK()
  • PERCENT_RANK()
  • NTILE()
 

ROW_NUMBER(): returns an integer value that represents the position of each row within its partition (group).

It takes two arguments: 1.) Order by column name, 2.) Order direction (ASC or DESC).

Row number function

RANK(): returns the rank of each row within its partition (group). It takes two arguments: 1.) Order by column name, 2.) Order direction (ASC or DESC).

Rank function

DENSE_RANK(): returns the rank of each row within its partition (group).

Dense rank function

PERCENT_RANK(): calculates percentiles relative to other values in an ordered set of values.

Percent rank function

NTILE(): divides the result set into a specified number of groups with equal numbers of rows, and assigns an integer value (starting at 1) to each group such that all groups have the same count.

Ntile function

Window functions: Focus on Window Analytical

Windowing Analytical functions

With our low/no-code editor, you need to select the Window Analytical operation, in the function parameter, you can choose in this dropdown list the relevant function:

  • CUME_DIST()
  • LAG()
  • LEAD()

 

CUME_DIST(): calculates the cumulative distribution of values from a single column across a partitioned table or view; it gives the percentage of total values less than or equal to any given value in the partitioned table or view.

Cum Dist function

LAG(): returns a value that is offset by rows before or after the current row.

The value returned by the function can be either a number or a string.

For example, if we have a table of employees and their salaries in months, we can use the lag function to get the previous month’s salary of an employee.

Lag function

LEAD(): returns the earliest row in a window that satisfies its arguments. The lag window function returns the last row in a window that satisfies its arguments.

Lead function

Now, you’re all set to use the window functions properly in your data analysis! 

Thanks to a low/no-code SaaS editor, anyone can analyze various and disparate datasets without being required to write code, Data & Ops teams don’t need to rely on the Tech team to get, process and analyze data.

If you’re interested in starting with connecting all your favorite tools, check out the RestApp website or book a demo.

Share

Subscribe to our newsletter

Brian Laleye
Brian Laleye
Brian is the co-founder of RestApp. He is a technology evangelist and passionate about innovation. He has an extensive experience focusing on modern data stack.
Share this article
Subscribe to our newsletter
Ready to experience data activation
without code?