SQL PARTITION BY makes SQL window functions – already a powerful analytical tool – even more suitable for advanced analytical insights from your data.

SQL is so ubiquitous in data analysis that even your grandmother knows how to aggregate data, sum it, or calculate the average values.

That’s all very nice and cute, but you want advanced analytical insights. SQL has a multitude of functions and clauses to handle complex queries. PARTITION BY is one of the more potent ones, enabling advanced data segmentation and analysis.

In this article, we’ll explore its syntax, use cases, and practical applications using examples from our platform.

In short, by the end of the article, you’ll be better at SQL than your granny.

What is the PARTITION BY Clause in SQL?

PARTITION BY is an optional clause in window functions. What are they, you might ask. Read a detailed answer in our SQL window functions guide.

In short, window functions are advanced analytics functions that perform operations on a window of rows and are able to aggregate and show individual rows simultaneously.

PARTITION BY adds another feature to the window functions: it divides a result set into partitions or subsets based on the criteria you define. In other words, PARTITION BY makes it possible to apply the window function on each data subset separately.

Basic Syntax and Usage PARTITION BY in SQL

In window functions, the OVER() clause is mandatory, as it defines the window function. PARTITION BY is a sub-clause of OVER(), as is ORDER BY. They are both optional clauses.

In the context of SELECT, here’s the PARTITION BY syntax.

SELECT column_1,
       column_2,
       window_function () OVER (PARTITION BY column_name ORDER BY column_name) AS column_alias
FROM table;

The syntax is simple: after PARTITION BY comes one or multiple column names by which you want to divide your data into subsets.

For example, if you have this table…

…and write this query…

SELECT employee_id,
       department_id,
       salary,
       AVG(salary) OVER (PARTITION BY department_id) AS avg_department_salary
FROM employees;

… you’d get this result.

In the query, I partitioned the result by the column department_id. So, in the output, all the data where department with ID = 1 is one partition, ID = 2 is the second, and ID = 3 is the third partition.

Because data is partitioned, the average salary is calculated for each department separately. For example, the average salary for the first department is 5,896.47. It’s calculated like this: (9,415.11 + 4,115.54 + 4,158.77)/3 = 17,689.42/3 = 5,896.47.

For every subsequent partition, the calculation resets and computes the average independently from the previous results.

So, there you have it! A simple example of how you can show all the data and calculate the average salary for each department using PARTITION BY.

Examples of Scenarios Where SQL PARTITION BY is Useful

PARTITION BY in SQL provides advanced analytical options. Here are 15 examples of where you may find it useful.

It’s far from an exhaustive list, as PARTITION BY has broad use.

1. Sales Running Totals: Summing sales for each salesperson per month.

2. Ranking Students: Rank students within each class based on the scores.

3. Moving Averages: Calculate moving averages (e.g.,5-day moving average) for stock prices of each stock.

4. Quarterly Growth Rates: Calculate quarter-over-quarter revenue growth by region.

5. Top Salespersons: Find the top five employees within each subsidiary based on sales.

6. Monthly Expense Totals: Calculate monthly expenses for each department.

7. Cumulative Sales: Calculate cumulative sales for each product category day by day.

8. Employee Tenure: Rank employees within each department by the length of their employment at the company.

9. Customer Order Frequency: Count the number of orders by each customer within a time period (e.g., month, quarter, year)

10. New Subscribers: Monthly track the number of new users within each subscription plan.

11. Departmental Budgets: Calculate the average budget by each department over multiple years.

12. Website Traffic Analysis: Analyze page views by user session.

13. Loan Repayments: Sum the loan repayment amount for each client.

14. Product Ratings: Rank products within the product category by their average rating.

15. Daily Temperature Averages: Calculate the temperature rolling average for every city.

Use Cases for SQL PARTITION BY

The PARTITION BY clause always does the same thing – divides the result set into data subgroups. How these partitions are used depends on the window function applied.

For example, you can use PARTITION BY with aggregate window functions, such as COUNT(), SUM(), AVG(), MIN(), or MAX(). By doing so, you aggregate data within the partition while keeping all the individual rows. Of course, the aggregation is performed for each partition separately.

The above is very different from GROUP BY, as it also allows you to aggregate data when used with one of the regular aggregate functions but collapses data into a single row per group.

In other words, PARTITION BY allows you to display aggregate and analytical data at the same time, while GROUP BY doesn’t.

Another common use of PARTITION BY is with the ranking window functions, i.e., ROW_NUMBER(), RANK(), DENSE_RANK(), PERCENT_RANK(), and NTILE(). These will rank data within each partition. You also need to use ORDER BY with the ranking functions to specify whether the ranking should be in ascending or descending order.

PARTITION BY is also useful with analytical window functions. For example, you can use LAG() or LEAD() to compare each row with the preceding or following rows, respectively. With FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE(), PARTITION BY can get you the value of the first, last, or nth row, respectively, within a partition.

PARTITION BY vs. GROUP BY

While PARTITION BY and GROUP BY might seem similar, they have very different uses due to their distinct characteristics.

SQL GROUP BY is primarily used with basic aggregate functions. This is great for summary statistics and reports, where you only need to show aggregated results without the row-level details.

On the other hand, PARTITION BY can be used with all the window functions (not only aggregate), so there’s a broader range of calculations possible. In conjunction with window functions, it’s ideal for analytical queries where complex calculations are required without losing row-level data.

To be more specific, you would use GROUP BY to show total sales per month and PARTITION BY to show daily sales while displaying monthly totals alongside.

Practical Applications of SQL PARTITION BY

Let’s now use several interview questions from our platform to demonstrate the SQL PARTITION BY clause’s practical applications.

Example #1: PARTITION BY With SUM() Window Function

The question by Amazon and Etsy wants you to calculate the percentage of the total spend a customer spent on each order. You should output the customer’s first name, order details, and percentage of the order cost to their total spend across all orders.

Link to the question: https://platform.stratascratch.com/coding/9899-percentage-of-total-spend

We’ll work with two tables. The first is orders.

The second one is customers.

Let’s first calculate the total amount each customer spent across all orders. We’ll do this using the SUM() window function with PARTITION BY.

In the SUM() function, we define the values we want to sum, total_order_cost, in this case. To make this function a window function, it’s necessary to use the OVER() clause. If left like this, the window function would return the total amount of all orders for all customers.

However, we need to do that for each customer separately, so we use PARTITION BY to split the result into partitions by the customer's first name.

SELECT c.first_name,
       o.order_details,
       SUM(o.total_order_cost) OVER (PARTITION BY c.first_name) AS total_cost_by_customer
FROM orders o
JOIN customers c ON c.id = o.cust_id;

Here are the first several rows of the output. It’s a list of all customers and their orders, with the total order value for each customer.

I can now write the final code. As I need a percentage of the total spend per order, I will divide each order's cost by the customer's total order cost. In addition, I’ll convert the result to FLOAT to show the result with decimal places.

SELECT c.first_name,
       o.order_details,
       o.total_order_cost/SUM(o.total_order_cost) OVER (PARTITION BY c.first_name)::FLOAT AS percentage_total_cost
FROM orders o
JOIN customers c ON c.id = o.cust_id;

There you have it: an output as required by the question.

Example #2: PARTITION BY With MAX() Window Function

Next, we have a question by MetLife and Credit Karma.

Link to the question: https://platform.stratascratch.com/coding/2003-recent-refinance-submissions

Here, we need to show all users and their loan balances for the most recent ‘Refinance’ submission.

We’ll again work with two tables. The first one is loans.

The second one is submissions.

The solution contains a subquery that shows the date of the most recent ‘Refinance’ submission for each user. It does so by using the MAX() window function with PARTITION BY.

The MAX() window function goes through the column created_at and returns the highest value, i.e., the latest date. PARTITION BY is there to create partitions on two criteria: user ID and loan type.

Since we need only the refinancing submissions, we filter data using WHERE.

SELECT DISTINCT id, 
                user_id, 
                created_at,
                MAX(created_at) OVER (PARTITION BY user_id) AS most_recent_refinance
FROM loans
WHERE type = 'Refinance';

Here’s the output.

I’ll now turn this query into a subquery and write the rest of the code.

To select the user ID and the balance in the main query, we join the subquery with the table submissions.

As we need the balance of the latest ‘Refinance’ submission, we equal the most recent refinance date from the subquery with created_at from submissions.

In the final step, the output is grouped by user ID and balance.

SELECT r.user_id,
       s.balance
FROM
  (SELECT DISTINCT id, 
       user_id, 
       created_at,
       MAX(created_at) OVER (PARTITION BY user_id) AS most_recent_refinance
   FROM loans
   WHERE type = 'Refinance') AS r
INNER JOIN submissions s 
ON r.id = s.loan_id
WHERE most_recent_refinance = created_at
GROUP BY r.user_id, s.balance;

The output shows three users and their respective loan balances.

Combining PARTITION BY With Other SQL features

PARTITION BY can also be used in conjunction with some other SQL features, such as:

• FILTER
• ORDER BY
• Frame clauses (ROWS BETWEEN and RANGE BETWEEN)
• CASE WHEN

Example #3: PARTITION BY With FILTER

The FILTER clause extends the aggregate window functions by giving the possibility of conditional aggregation within each partition.

Let’s revisit the previous example and rewrite the code using FILTER.

The code stays virtually unchanged. The main difference is that the condition WHERE type = 'Refinance' from the subquery is now moved to a window function itself.

SELECT r.user_id,
       s.balance
FROM
  (SELECT DISTINCT id, 
                   user_id, 
                   created_at,
                   MAX(created_at) FILTER (WHERE type = 'Refinance') OVER (PARTITION BY user_id) AS most_recent_refinance
   FROM loans) AS r
INNER JOIN submissions s 
ON r.id = s.loan_id
WHERE r.most_recent_refinance = r.created_at
GROUP BY r.user_id, s.balance;

Example #4: PARTITION BY With ORDER BY

When I say ORDER BY, I mean the optional window function clause used for sorting the data within a partition.

By optional, I mean it’s not mandatory for some aggregate window functions, but it can be used in some instances. However, it is required for ranking and value window functions to work.

Here’s one such example in the question by Redfin.

Link to the question: https://platform.stratascratch.com/coding/2022-update-call-duration

We need to find the average call duration for all update calls.

Here, we have a table named redfin_call_tracking.

Let’s start writing the solution from the second subquery, this SELECT statement.

SELECT *,
       RANK() OVER (PARTITION BY request_id ORDER BY created_on ASC) AS rk
FROM redfin_call_tracking;

It selects all the columns from the table and ranks the calls within each partition by date. The window function we use is RANK(). The result set is partitioned by the request ID. To make sure the calls for each request are ranked from the earliest to the latest date, we order the data within the partition in ascending order.

Why do we do all that? Because whichever call is ranked as first is an initial call. All other calls are update calls. You’ll see in the next step why we need this when we write the above SELECT as a subquery.

So, this is the complete code.

There’s another subquery referencing the above SELECT to get only the call IDs with a ranking above 1, which keeps only the update calls, as the initial call will always be ranked 1.

All this is then used in the main query to calculate the average duration of all these update calls.

SELECT AVG(call_duration) AS avg_call_duration
FROM redfin_call_tracking
WHERE id IN
    (SELECT id
     FROM
       (SELECT *,
               RANK() OVER (PARTITION BY request_id ORDER BY created_on ASC) AS rk
        FROM redfin_call_tracking) AS sq
     WHERE rk > 1);

The code returns an average of 17.882.

Example #5: PARTITION BY With Frame Clauses

ROWS BETWEEN and RANGE BETWEEN are frame clauses in window functions used for defining a specific subset of rows within the partition.

To show what I mean by that, I’ll use the previous question as a basis, but I’ll calculate a moving average of call duration for each request.

The moving average is calculated using the AVG() window function, as you might expect. As in the previous example, I partitioned the data by the request ID and ordered it by the date.

I need the ROWS BETWEEN clause to turn this into a moving average. It tells the AVG() function to include the current row’s and the previous two rows’ call duration. You can specify any number of rows before and/or after the current row in your moving average calculation as long as it makes sense.

SELECT *,
       AVG(call_duration) OVER (PARTITION BY request_id ORDER BY created_on ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_call_duration
FROM redfin_call_tracking;

Here are the first several rows of the output.

The moving average in the first row is the same as the call duration. Because there are no preceding rows, the calculation can only consider the current row: 28/1 = 28.

In the second, the moving average includes the current and the previous row: (28+12)/2 = 20. Only one preceding row is considered because there are no two preceding rows available.

The third row can finally involve the current and the preceding two rows: (28+12+19) = 19.667.

As we move further down the data, the window frame also moves to always include the current row and two previous rows. So, the next calculation is (12+19+28) = 19.667.

The next moving average is (19+28+13) = 20, and so on. Once we get to the next request ID, the calculation will reset as the moving average is calculated for each partition separately.

Example #6: PARTITION BY With CASE WHEN

The CASE WHEN statement is SQL’s way of handling the IF-THEN logic; it returns data that satisfies the specific criteria.

This can be utilized to perform conditional aggregation in the context of window functions. I’ll show you an example in this Uber interview question.

Link to the question: https://platform.stratascratch.com/coding/10013-positive-ad-channels

However, I’ll change the question’s requirements. So, imagine an advertising channel is considered high-yield when it acquires over 1,500 customers in a year.

The task is to show the total number of customers each channel acquired as a high-yielding channel. In other words, we want to include only values that are above 1,500.

We have the table uber_advertising at our disposal.

Here’s the code.

I used CASE WHEN to include in the sum only those rows with more than 1,500 customers acquired. Then, I partitioned the results by advertising channel.

SELECT *,
       SUM(CASE WHEN customers_acquired > 1500 THEN customers_acquired ELSE 0 END) OVER (PARTITION BY advertising_channel) AS high_yield_customers_acquired
FROM uber_advertising
ORDER BY advertising_channel, year;

Take a look at the output.

For example, billboards and buses acquired 5,900 and 7,500 customers in total, respectively. However, for bus stops, we have 0, as this channel never went above 1,500 customers in any of the three years. The same is true with the radio.

Handling NULL Values and Outliers

The NULL values and outliers can sometimes significantly skew your analysis. So, knowing how to exclude them when using PARTITION BY is important.

Example #7: Handling NULL Values

The NULL values are typically handled in the WHERE clause.

There’s a question by Google and Deloitte that shows this.

Link to the question: https://platform.stratascratch.com/coding/2099-election-results

To solve the question, we must find out who got the most votes and won the election.

We’re given the voting_results table.

The first select statement uses the COUNT() window function and PARTITION BY to calculate the number of votes each voter gave. This result then divides 1 to get a vote value, as each voter can vote for multiple candidates.

There are also the voters who didn’t vote for anybody; these will have the candidate value shown as NULL. We need to exclude those rows; otherwise, they will be treated as a voice for a NULL candidate. No, there’s no candidate named NULL! So, we exclude it in WHERE.

SELECT voter,
       candidate,
       1.0 / COUNT(*) OVER (PARTITION BY voter) AS vote_value
FROM voting_results
WHERE candidate IS NOT NULL;

I can now turn this into a subquery and write the rest of the code. There’s another subquery that builds on this one.

It sums the vote value and rounds it to the third decimal, as instructed by the question. Then, it takes this vote value and ranks the candidates. DENSE_RANK() is used, so the candidates with the same number of votes will be ranked the same, and there will be no gaps between the ranks in case of ties.

The main query then just filters data to show only the candidates who are ranked as the first.

SELECT candidate
FROM
  (SELECT candidate,
          ROUND(SUM(vote_value), 3) AS n_votes,
          DENSE_RANK() OVER (ORDER BY ROUND(SUM(vote_value), 3) DESC) AS place
   FROM
     (SELECT voter,
             candidate,
             1.0 / COUNT(*) OVER (PARTITION BY voter) AS vote_value
      FROM voting_results
      WHERE candidate IS NOT NULL) AS value
   GROUP BY candidate) AS results
WHERE place = 1;

The winner of these elections is Christine.

Example #8: Handling Outliers

Here, we have a great example of how you can handle outliers using PARTITION BY.

Salesforce and Glassdoor ask you to compare each employee’s salary with the average salary of the corresponding department.

However, I want to make this calculation more realistic, so I will exclude the outliers from the calculation. Not doing that can skew the results, e.g., a manager with an extremely high salary can artificially increase the average salary of the generally lowly-paid department.

Link to the question: https://platform.stratascratch.com/coding/9917-average-salaries

We have only one table, namely employee.

The first step is to use the PERCENTILE_CONT() function and WITHIN GROUP clause in a CTE to calculate each department's 95th percentile.

PERCENTILE_CONT() stands for continuous percentile and is an aggregate function for calculating the nth percentile you define in the function parentheses.

The WITHIN GROUP clause defines the order of the values in the percentile calculation; in this case, it’s employee salary sorted in ascending order.

WITH percentiles AS (
  SELECT department,
         PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY salary) AS p95_salary
  FROM employee
  GROUP BY department
),

Running this CTE as a simple SELECT returns this output.

SELECT department,
             PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY salary) AS p95_salary
FROM employee
GROUP BY department;

Now that we have 95th percentiles for each department, we can write another CTE that removes all the individual salaries that are above the 95th departmental percentile.

WITH percentiles AS (
  SELECT department,
         PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY salary) AS p95_salary
FROM employee
GROUP BY department),

filtered_salaries AS (
  SELECT e.department,
         e.first_name,
         e.salary
  FROM employee e
  JOIN percentiles p
  ON e.department = p.department
  WHERE e.salary <= p.p95_salary
)

To write the final solution, you just need to add SELECT with the AVG() window function and partition data by department.

WITH percentiles AS (
  SELECT department,
         PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY salary) AS p95_salary
FROM employee
GROUP BY department),

filtered_salaries AS (
  SELECT e.department,
         e.first_name,
         e.salary
  FROM employee e
  JOIN percentiles p
  ON e.department = p.department
  WHERE e.salary <= p.p95_salary
)

SELECT department,
       first_name,
       salary,
       AVG(salary) OVER (PARTITION BY department) AS avg_salary_by_department
FROM filtered_salaries;

Here’s the output.

Now, the above code can be simplified, but our database doesn’t allow using OVER() with PERCENTILE_CONT(). Nevertheless, here’s the simplified code that should give you the same output as the one above.

WITH percentiles AS (
  SELECT department,
               first_name,
               salary,
               PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY department) AS p95_salary
  FROM employee
)

SELECT 
  department, 
  first_name, 
  salary, 
  AVG(salary) OVER (PARTITION BY department) AS avg_salary_by_department
FROM 
  percentiles
WHERE 
  salary <= p95_salary;

Best Practices for Efficient Queries

Yes, SQL PARTITION BY does allow you to get very complex insights from your data. However, as your queries become more complex, there’s a danger of them becoming less efficient.

Here’s some advice on how to avoid that when using PARTITION BY.

1. Indexing: Leverage indexing on columns that you use in PARTITION BY.

2. Limited Partitioning: Don’t overdo the partitioning, as it can decrease the query performance. Use PARTITION BY only when necessary.

3. Combine PARTITION BY and  WHERE: Whenever possible, filter data using WHERE before partitioning it. This reduces the size of a dataset PARTITION BY works with.

Limitations and Caveats

Everything has its limitations, and SQL PARTITION BY is not above that. These are the three most important limitations and caveats you should consider.

1. Performance: With very large datasets, PARTITION BY can be resource-intensive.

2. Complexity: Partitioning datasets is a complex operation. You should have this in mind and carefully design queries to avoid performance bottlenecks.

3. Database Support: Not all SQL databases fully support advanced window functions. You saw that in the previous example, where our PostgreSQL version doesn’t support using OVER() with PERCENTILE_CONT(). In addition, the syntax of the window functions between some databases can differ.

Conclusion

When the PARTITION BY and window functions work together, they enhance each other’s possibilities. Used together, they allow you to rank, aggregate, and analyze data, while also adding multiple dimensions to analysis with data partitioning.

The practical applications are almost infinite, accompanied by the unique ability you can’t have any other way in SQL. It is the ability to show analytical and aggregate data simultaneously.

Due to this, they are also very popular in the SQL interview questions. Don’t waste your time and start practicing PARTITION BY and window function; you can find them among the many coding questions we have.

Also, visit our blog and YouTube channel for more resources on PARTITION BY and other important SQL topics.