Running totals

SQL · Unit 13: Window functions

Full plan
SQL
9 exercises

The owner keeps track of units ordered. After each order, how many units had been ordered so far?

A SUM with an ORDER BY inside OVER keeps a running total. Last lesson, that ORDER BY handed out numbers. With an aggregate it does something else: it trims the window. Each row no longer sees every row. It sees the rows from the first one up to itself, sorted that way. Order 3 sees three orders, and they add up to 8.

Only the first six orders are shown, so the result fits on the screen.

SELECT id, quantity,
  SUM(quantity) OVER (
    ORDER BY id
  ) AS running_total
FROM orders
WHERE id <= 6
ORDER BY id;

Returns

id	quantity	running_total
1	2	2
2	1	3
3	5	8
4	10	18
5	1	19
6	2	21

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
Luke, customer 2, has orders 3, 6, and 12, of 5, 2, and 1 units. What does this query return?
2. Put the lines in order
The Cable, product 4, is the most ordered thing in the store. How many Cables had been ordered after each order? Put in order the query that adds them up by order number and shows the most recent first. Two lines don't belong.
3. Predict the output
Orders 7 through 11 span three days. On January 12, order 7 came in, for 1 unit, and order 8, for 3. On the 14th, order 9, for 1. On the 15th, order 10, for 2, and order 11, for 1. What does this query return?
4. Complete the code
What was the biggest order the store had received up to each moment? Fill in the window so that next to each of the first five orders you get that record, as they came in.
5. Find the bug
The owner wants to see that summary with the most recent day on top. Someone flipped the sort, and now the first day of sales, January 3, says the store was up to 30 units. Which line has the problem?
6. Multiple choice
The owner looks in that summary for how many units the store was up to at the close of January 5. There were orders on January 3, 4, and 9, and none between the 4th and the 9th. What does the summary show?
7. Put the lines in order
After each of their orders, how many units had each customer ordered so far? Put in order the query that shows this for Ann and Luke, customers 1 and 2, with Luke first. Two lines don't belong.
8. Find the case that fails
The owner is tracking Ann, customer 1. Each row has to say how many units Ann was up to after that order, going by when she placed them. This query adds up by order number, and today it gets it right. Which of these changes breaks it?
9. Write the code
How did sales grow, order by order? Write a query with three columns: first the order's number, then its date, then how much money the store had taken in once that order was in. An order is worth p.price * o.quantity. Add them up by date and, within the same day, by order number. Show the rows the other way around, with the most recent order on top.

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 →