MASTERING SQL FRIEDS WORKS | Looking at the data science

In my work, I have written many SQL questions to release information from the information. It is always a challenging job because it is not only important to write effective questions, but it is also easy enough to keep time later.

For each new problem comes up a new lesson, and recently, I have been in the functions of the SQL windows. These powerful tools are very useful when you need to make counts across the set of lines Without losing the granulously of the records.

In this article, I will go down the activities of SQL Window step. They can seem to be complicated or different at first, but when you understand how they work, you will see how important they are. You're ready? Let's get in and know well together!

Content

• Why do we need Windows jobs?
• Windows Work Syntax
• Four simple examples

Why do we need Windows jobs?

To understand the power of Windows functions, let's start with a simple example. Imagine that you have a table containing six orders from the e-commerce website. Each row includes an order ID, day, product, its nature and price.

Suppose we want to count the total number of each product. You use Team by The paragraph, we can write a question like this:

SELECT 
      brand, 
      SUM(price) as total_price 
FROM Orders 
GROUP BY brand

This returns the result when each line represents one type, and the total number of all orders under that type.

|brand  |total_price|
|-------|-----------|
|carpisa|30         |
|nike   |175        |
|parfois|25         |
|zara   |65         |

This combination removes information on individual orders, because out of opt of a single product line. What if we want to keep all the original lines and put the total number of individual products such as the extra field?

Through SUM(price) OVER (PARTITION BY brand)We can calculate the price of each product Without falling:

SELECT 
    order_id,
    date,
    product,
    brand,
    price,
    SUM(price) OVER (PARTITION BY brand) as total_price
FROM Orders

We found a result such as:

|order_id|date      |product|brand  |price|total_price|
|--------|----------|-------|-------|-----|-----------|
|6       |2025/05/01|bag    |carpisa|30   |30         |
|1       |2024/02/01|shoes  |nike   |90   |175        |
|3       |2024/06/01|shoes  |nike   |85   |175        |
|5       |2025/04/01|bag    |parfois|25   |25         |
|2       |2024/05/01|dress  |zara   |50   |65         |
|4       |2025/01/01|t-shirt|zara   |15   |65         |

This question returns all six lines, and maintains all each order, and adds a new column to show the perfect price for each product. For example, the order with the Carpisa product displays 30, because the end of the Carpisa Order, two instructions from Nike Show 175 (90 + 85), and so on.

You may notice that the table is still unique to an order_id. That is because Windows work is separated by the product, and SQL does not guarantee line order unless it is clearly defined. Restoring the original order, we need to simply add an ORDER BY Clause:

SELECT 
    order_id,
    date,
    product,
    brand,
    price,
    SUM(price) OVER (PARTITION BY brand) as total_price
FROM Orders
ORDER BY order_id

Finally, we have the release that contains all required information:

|order_id|date      |product|brand  |price|total_price|
|--------|----------|-------|-------|-----|-----------|
|1       |2024/02/01|shoes  |nike   |90   |175        |
|2       |2024/05/01|dress  |zara   |50   |65         |
|3       |2024/06/01|shoes  |nike   |85   |175        |
|4       |2025/01/01|t-shirt|zara   |15   |65         |
|5       |2025/04/01|bag    |parfois|25   |25         |
|6       |2025/05/01|bag    |carpisa|30   |30         |

Now, add similar combination as GROUP BYWhile keeping all the data of each person's order information.

Windows functions of Windows

Usually, Windows work has a syntax that looks like this:

f(col2) OVER(
[PARTITION BY col1] 
[ORDER BY col3]
)

Let's break. f(col2) The operation you want to do, such as the sum, count, and position. OVER The section of the Act describes the “window” or subset lines where Windows work works. PARTITION BY col1 Divide the details in groups and ORDER BY col1 determines the order of lines within each grade.

In addition, Windows activities fall into three main categories:

• Combined work:COUNT, SUM, AVG, MINincluding MAX
• Work to go up: ROW_NUMBER, RANK, DENSE_RANK, CUME_DIST, PERCENT_RANKincludingNTILE
• Numbered Work: LEAD, LAG, FIRST_VALUE including LAST_VALUE

Four simple examples

Let us show a variety of examples of Windows activities.

Example 1: Soft Windows Work

Understanding the idea of ​​the window work, first, for the exact example. Suppose we want to calculate the total number of all orders on the table. Using a GROUP BY Clause will give us one amount: 295. However, that will fall down the lines and lose the information for each order. Instead, if we want to show the perfect price next to each record, we can use the Windows work like this:

SELECT 
    order_id,
    date,
    product,
    brand,
    price,
    SUM(price) OVER () as tot_price
FROM Orders

This is the result:

|order_id|date      |product|brand  |price|tot_price|
|--------|----------|-------|-------|-----|---------|
|1       |2024-02-01|shoes  |nike   |90   |295      |
|2       |2024-05-01|dress  |zara   |50   |295      |
|3       |2024-06-01|shoes  |nike   |85   |295      |
|4       |2025-01-01|t-shirt|zara   |15   |295      |
|5       |2025-04-01|bag    |parfois|25   |295      |
|6       |2025-05-01|bag    |carpisa|30   |295      |

This way, we got the sum of all prices over all the details and repeatedly in each line.

Example 2: Division of subsection

Now let's count the average amount per year while storing all the details. We can do this through PARTITION BY Clause within the Windows work in the group line per year and includes each party's average:

SELECT 
    order_id,
    date,
    product,
    brand,
    price,
    round(AVG(price) OVER (PARTITION BY YEAR(date) as avg_price
FROM Orders

Here is the twinkling of what looks like:

|order_id|date      |product|brand  |price|avg_price|
|--------|----------|-------|-------|-----|---------|
|1       |2024-02-01|shoes  |nike   |90   |75       |
|2       |2024-05-01|dress  |zara   |50   |75       |
|3       |2024-06-01|shoes  |nike   |85   |75       |
|4       |2025-01-01|t-shirt|zara   |15   |23.33    |
|5       |2025-04-01|bag    |parfois|25   |23.33    |
|6       |2025-05-01|bag    |carpisa|30   |23.33    |

That is good! We see the average price of each year next to each line.

Example 3: Organized in subsection

One of the best ways to understand how to order it works within Windows activities to install a gun.com work. Suppose we want to measure all orders from highest with low price. Here's how we can do the use of the RANK() Work:

SELECT 
    order_id,
    date,
    product,
    brand,
    price,
    RANK() OVER (ORDER BY price DESC) as Rank
FROM Orders

We get a result like this:

|order_id|date      |product|brand  |price|Rank|
|--------|----------|-------|-------|-----|----|
|1       |2024-02-01|shoes  |nike   |90   |1   |
|3       |2024-06-01|shoes  |nike   |85   |2   |
|2       |2024-05-01|dress  |zara   |50   |3   |
|6       |2025-05-01|bag    |carpisa|30   |4   |
|5       |2025-04-01|bag    |parfois|25   |5   |
|4       |2025-01-01|t-shirt|zara   |15   |6   |

As shown, the highest order receives position 1, and others followed in order.

Example 4: Combine partition with a group with phrases

In the previous example, we were assigned to all instructions from very top to low price for all details. But what if we want to restart each year position? We can do this by adding PARTITION BY Class of window work. This allows partition information into separate groups and filters highest orders to low price.

SELECT 
    order_id,
    date,
    product,
    brand,
    price,
    RANK() OVER (PARTITION BY YEAR(date) ORDER BY price DESC) as Rank
FROM Orders

The result should look like this:

|order_id|date      |product|brand  |price|Rank|
|--------|----------|-------|-------|-----|----|
|1       |2024-02-01|shoes  |nike   |90   |1   |
|3       |2024-06-01|shoes  |nike   |85   |2   |
|2       |2024-05-01|dress  |zara   |50   |3   |
|6       |2025-05-01|bag    |carpisa|30   |1   |
|5       |2025-04-01|bag    |parfois|25   |2   |
|4       |2025-01-01|t-shirt|zara   |15   |3   |

Now, the restart of office year after year, as we decided.

Last thoughts:

I hope that this guide helped you to get a clear and practical launch of SQL windows. At first, they can feel different, but if you compare it on the side and the GROUP BY Clause, the number of simulates is very easy to understand.

According to my own experience, Windows activities have great power by issuing information without losing the quality of level, something that is subtle. It is a great use of issuing the Metrics such as prices, levels, year after continuous year or month of the month.

However, there is some limitations. Windows activities can be very expensive, especially on top of capital details or complex partition. It is important to check that additional flexibility allows the active trade in your specific use of use.

Thanks for reading! Have a nice day!

Useful Resources: