Learn SQL: Running totals and LAG
The standings
Every evening at six the press wants the standings: who leads after this stage, and how much has he piled up? "Not just the final classification, but how it unfolded," says Griet. A total that grows with every stage.
A total that grows along
Put an ORDER BY in the OVER of a SUM, and the window grows along: from the first row up to and including the current one. That gives you a running total.
Here are Ruben de Graaf's points, stage after stage, with his total so far next to them.
SELECT stage_id, points, SUM(points) OVER (ORDER BY stage_id) AS total FROM results WHERE rider_id = 11| stage_id | points | total |
|---|---|---|
| 1 | 12 | 12 |
| 2 | 0 | 12 |
| 3 | 4 | 16 |
| 4 | 15 | 31 |
| 5 | 0 | 31 |
A quick look back with LAG
LAG(column) fetches the value from the previous row of the window, LEAD(column) from the next one. The first row has no previous row, and gets NULL.
Here is the difference in distance from the stage before.
SELECT id, finish, km, km - LAG(km) OVER (ORDER BY id) AS diff FROM stages| id | finish | km | diff |
|---|---|---|---|
| 1 | Lennik | 164 | NULL |
| 2 | Lennik | 18 | -146 |
| 3 | Galmaarden | 178 | 160 |
| 4 | Pepingen | 185 | 7 |
| 5 | Halle | 156 | -29 |
Try it yourself
Show per stage the finish, the distance and how many kilometres the riders have covered in total after that stage.
Ready for more?
The full lesson has 6 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson