Skip to the content
Start

PARTITION BY

SQL · Unit 13: Window functions

Is a Keyboard at 45.5 expensive? In unit 9, the answer was to put its section's average next to each product. That took a subquery in the SELECT, worked out once for every row, with a copy of the table called x.

This query returns the same five rows. PARTITION BY, inside the parentheses of OVER, splits the rows into groups, one for each value of the column, the way GROUP BY did. Each row's window stops being the whole result and becomes its group. The average next to the Keyboard is the accessories' average.

SELECT name, price,
  AVG(price) OVER (
    PARTITION BY category
  ) AS section_avg
FROM products
WHERE price < 100;

Returns

namepricesection_avg
Keyboard45.523.55
Mouse19.923.55
Cable5.2523.55
Headphones59.966.95
Speaker7466.95

The rest of the explanation is in the lesson, which is part of the full plan.

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

  1. 1. Predict the output

    For each product, how many products are in its section? Four products cost less than 60: the three accessories and the Headphones, which are in Audio. The other Audio product, the Speaker, costs 74. What does this query return?

  2. 2. Predict the output

    Sales, department 1, has Mark at 3800, Rachel at 1900, and Nicole at 2100. Design, department 3, has only Grace, at 2400. What does this query return?

  3. 3. Multiple choice

    HR has four questions about salaries. Which one calls for a window and not a GROUP BY?

  4. 4. Put the lines in order

    How much is each department's payroll, meaning what everyone who works in it earns put together? Put in order the query that gives a single row per department, the biggest payroll first. Carol, who has no department, doesn't count. Two lines don't belong.

  5. 5. Complete the code

    What share of their department's payroll goes to each person? It's the calculation from last lesson, with the department's total instead of the whole table's. Fill in the aggregate and the part that splits the rows into groups. Only Sales and Design are included, departments 1 and 3.

  6. 6. Find the bug

    How many people does each person share a department with, counting themselves? Sales has three, Tech four, and Design one. The query runs and says everyone works alone. Which line has the problem?

  7. 7. Put the lines in order

    HR is putting together the list for Sales and Tech, departments 1 and 2. Put in order the query that gives each person with their department and, next to it, the highest salary in that department, in alphabetical order. Two lines don't belong.

  8. 8. Predict the output

    The same query with the < changed to >. Carol earns 5200 and has no department. Mark earns 3800 in Sales, which averages 2600. Paige earns 4100 in Tech, which averages 3175. Grace is alone in Design. What does it return?

  9. 9. Find the bug

    Who earns more than the average of the whole company? Carol, Paige, and Mark should be on the list. Here SQL answers misuse of window function AVG(). Which line has the problem?

  10. 10. Write the code

    HR wants the full list. Write a query with three columns, in this order: the name, the salary, and how much each person earns above their department's average, negative if they earn less. Carol, with no department, is compared with herself. All nine have to be on the list, in alphabetical order.

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →