>_ IzzyLab

Hands-on task

This task is step 17 of the course “SQL with SQLite”

SQLite: running total

medium~7 min

The database /root/shop.db has a table sales(day INTEGER, amount INTEGER) — revenue by day. Write a query with the window function SUM(...) OVER (ORDER BY ...) that, for each day, produces a running total (the sum of revenue from the first day through the current one), and write the result to /root/running.txt in the format day|amount|total.

What you'll practise:
  • window functions
  • running total
  • sum over
  • order by
  • sqlite3

Topic breakdown

A running total is the sum of all values from the start of the set up to and including the current row. A plain aggregate SUM(...) with GROUP BY would collapse everything into a single number, so the window form SUM(...) OVER (...) is used, which computes the sum but leaves the rows in place. The key is the ORDER BY inside OVER (...): when present, SQLite by default takes the window frame "from the start to the current row", which yields the accumulation. Columns in sqlite3 output are separated by |.

What's checked

  1. first day's total equals its revenue
  2. last day's total equals the sum of all days

What it looks like