OVER

SQL · Unit 13: Window functions

Full plan
SQL
9 exercises

Luke, customer 2, has placed three orders, of 5, 2, and 1 units. How much of everything he's ordered does each one account for? To answer, you need the total, 8, next to each order. And that's what an aggregate wouldn't let you do. In unit 5 you saw that a single aggregate in the SELECT turns the whole query into a summary, and three rows become one.

OVER (), right after the aggregate, breaks that habit. The SUM adds up the same thing and, instead of collapsing the rows, puts the result next to each one. All three are still there.

SELECT id, quantity,
  SUM(quantity) OVER () AS total
FROM orders
WHERE customer_id = 2;

Returns

id	quantity	total
3	5	8
6	2	8
12	1	8

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. Predict the output
In unit 5, someone had the list of the three accessories and wanted to add a column saying how many there are. The COUNT(*) left a single row. In unit 9 the fix was a subquery that counted again for every product. This is the third way. What does it return?
2. Predict the output
On January 12 two orders came in: order 7, for 1 unit, and order 8, for 3. Across the whole store, 30 units have been ordered. What does this query return?
3. Complete the code
Ann, customer 1, has placed three orders, of 2, 1, and 1 units. Each one needs the biggest of the three next to it. Fill in the aggregate and the part that makes it leave the rows as they are.
4. Put the lines in order
The stock clerk is counting the Accessories section. Put in order the query that gives each accessory with its units and, next to it, what the whole section adds up to, from the one with the most to the one with the least. Two lines don't belong.
5. Complete the code
The question this lesson opened with. What share of Luke's 8 units was each order? Fill in the calculation so it comes out as a percentage, decimals included.
6. Find the bug
Valerie, customer 5, has placed two orders, of 1 and 3 units. Someone wants to know what share each one is. The query runs and returns a single row. Which line has the problem?
7. Put the lines in order
The Cable, product 4, is the most ordered thing in the store. How much of the 30 units ordered do its orders account for? Put in order the query that gives the quantity of each Cable order next to the total for the whole store, from the smallest to the biggest. Two lines don't belong.
8. Multiple choice
Not everything that mentions a total calls for a window. Which of these questions is answered with a plain aggregate, with no OVER?
9. Write the code
What share of what the store has sold comes from each customer? The per_customer step is already written. It's the summary from unit 10, with what each customer who has ordered something has spent. Below it, write the query with three columns, in this order: the name, the money, and the share of the total that money is, as a percentage rounded to one decimal. Whoever has spent the most goes first.

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 →