Percent of total with window functions in SQL - Time & Space Complexity

Time Complexity: Percent of total with window functions

O(n)

Understanding Time Complexity

When we use window functions to calculate percent of total, we want to know how the work grows as the data grows.

How does the time to run the query change when the number of rows increases?

Scenario Under Consideration

Analyze the time complexity of the following SQL query using window functions.

SELECT
  category,
  sales,
  sales * 100.0 / SUM(sales) OVER () AS percent_of_total
FROM
  sales_data;
    

This query calculates each category's sales and what percent it is of the total sales.

Identify Repeating Operations

Look for repeated work done by the query.

• Primary operation: Scanning all rows once to compute the total sales and then computing percent for each row.
• How many times: The total sum is computed once, and then each row is processed once to calculate the percent.

How Execution Grows With Input

As the number of rows grows, the total sum calculation scans all rows once, then each row gets its percent calculated.

Input Size (n)	Approx. Operations
10	About 20 operations (10 to sum, 10 to calculate percent)
100	About 200 operations
1000	About 2000 operations

Pattern observation: The work grows roughly in direct proportion to the number of rows.

Final Time Complexity

Time Complexity: O(n)

This means the time to run the query grows linearly as the number of rows increases.

Common Mistake

[X] Wrong: "Using window functions means the query runs multiple times for each row, so it's much slower than a simple sum."

[OK] Correct: The window function computes the total sum once over all rows, then applies the calculation per row without repeating the full sum each time.

Interview Connect

Understanding how window functions scale helps you explain query performance clearly and shows you know how databases handle calculations efficiently.

Self-Check

What if we changed the window function to partition by category? How would the time complexity change?