# Build an Expense Analytics Report (SQL)

> Go from a raw expenses table to a real monthly report in SQL - grouping, aggregates, and window functions - all runnable in your browser.


---

# Build an Expense Analytics Report (SQL)

You have a pile of expenses. A few dozen rows: groceries, rent, the streaming
subscription you keep meaning to cancel. Someone - your future self, a manager,
a spouse - wants to know where the money went. Not the raw list. The story.
How much per category. How each month compares to the last. Whether spending is
creeping up.

That story is a SQL report, and over this weekend you're going to build it from
nothing. By the last phase you'll have one query that answers all of those
questions at once, and you'll understand every line of it.

## What you'll build

A single expense analytics report, assembled in four steps:

1. A `expenses` table with realistic seed data you can query.
2. Spend broken down by category and by month, using `GROUP BY` and `SUM`.
3. A running total and a month-over-month change, using window functions
   (`SUM() OVER` and `LAG`).
4. One final report query that ties the pieces together - category share of
   total, plus the monthly trend - that you could drop into a real dashboard.

## The stack

SQLite. That's it. No server to install, no account to create, no schema
migrations. **This whole project runs in your browser** - every code block on
these pages has a Run button. Press it and the SQL executes against a fresh,
in-memory SQLite database right on the page, and you see the rows it returns.

Because each block runs in isolation, every runnable block on every page
re-creates the table and re-inserts the data before it queries. That's
deliberate. It means you can run any block on its own, in any order, and always
get a result - and it means you can change a number, re-run, and immediately
see what moved.

## Roughly how long

A focused weekend afternoon - two to three hours if you run every block and
poke at the queries, which you should. None of the phases are long. The point
isn't volume; it's that each one leaves you with a working piece you understand.

## What you'll learn

| Phase | The technique | Why it matters |
|-------|---------------|----------------|
| 1 | `CREATE TABLE`, `INSERT`, `SELECT` | Get data in and look at it |
| 2 | `GROUP BY`, `SUM`, `ORDER BY` | Collapse rows into totals |
| 3 | `SUM() OVER`, `LAG`, frames | Compute across rows without losing them |
| 4 | Subqueries + the pieces combined | Turn techniques into a real report |

If you've written a `SELECT * FROM` before but `GROUP BY` still feels fuzzy and
window functions feel like wizardry, this is aimed squarely at you. By the end,
neither one will.

## How to use these pages

Read a phase, then run the block. Then change something - add an expense, swap a
category, shift a date into a different month - and run it again. SQL clicks
when you watch the output move in response to the input. The browser makes that
loop instant, so use it.

Let's build the table.


---

# The Schema and Seed Data

Every report needs something to report on. Before any analytics, you need a
table and rows in it. That's this phase: design a small `expenses` table, pour
in a realistic month-and-a-half of spending, and confirm it's all there.

## What an expense looks like

Strip a real expense down to what a report cares about and you get four things:

- **When** it happened - a date.
- **What** it was for - a category like groceries or rent.
- **A short description** - the merchant or memo, so a row is recognizable.
- **How much** - the amount.

That maps cleanly to four columns. Here's the table:

```sql
CREATE TABLE expenses (
  id          INTEGER PRIMARY KEY,
  spent_on    TEXT    NOT NULL,   -- ISO date: 'YYYY-MM-DD'
  category    TEXT    NOT NULL,
  description TEXT    NOT NULL,
  amount      REAL    NOT NULL    -- dollars, e.g. 42.50
);
```

A few decisions worth naming, because you'll feel them later:

**Dates as `TEXT` in `YYYY-MM-DD` form.** SQLite has no dedicated date type, and
it doesn't need one. As long as you store dates in ISO order, they sort
correctly as plain strings and SQLite's date functions read them happily. The
string `'2026-02-09'` sorts after `'2026-01-30'` exactly the way the calendar
does. Store dates any other way and you'll fight it forever; store them this way
and everything downstream behaves.

**Amount as `REAL`.** For money in a real production system you'd use integer
cents to dodge floating-point rounding. For a personal report where you're
eyeballing totals, `REAL` keeps the SQL readable and the rounding errors stay
below a cent. We'll note where it matters.

**`category` as a plain text column.** No separate categories table, no foreign
key. With a couple dozen rows and a handful of categories, a lookup table would
be machinery you don't need yet. The cost is that a typo (`grocery` vs
`groceries`) becomes its own category - so keep the spellings consistent when
you add rows.

## The seed data

Now the rows. Here's roughly six weeks of spending across January and February -
the kind of mix a real month has: a big rent payment, recurring subscriptions,
a scatter of groceries and dining, one travel splurge.

Run this block. It creates the table, inserts everything, then selects it back
sorted by date so you can see what you're working with.

Before you run it, guess: how many rows come back, and which expense is first?

```sql runnable
CREATE TABLE expenses (
  id          INTEGER PRIMARY KEY,
  spent_on    TEXT    NOT NULL,
  category    TEXT    NOT NULL,
  description TEXT    NOT NULL,
  amount      REAL    NOT NULL
);

INSERT INTO expenses (spent_on, category, description, amount) VALUES
  ('2026-01-01', 'rent',          'January rent',        1450.00),
  ('2026-01-02', 'subscriptions', 'Streaming service',      15.99),
  ('2026-01-03', 'groceries',     'Corner market',          54.20),
  ('2026-01-05', 'dining',        'Lunch with Sam',         28.75),
  ('2026-01-07', 'transport',     'Metro card refill',      40.00),
  ('2026-01-09', 'groceries',     'Weekly shop',            96.40),
  ('2026-01-12', 'utilities',     'Electricity',            72.10),
  ('2026-01-14', 'dining',        'Pizza night',            34.50),
  ('2026-01-16', 'subscriptions', 'Music service',           9.99),
  ('2026-01-18', 'groceries',     'Farmers market',         61.30),
  ('2026-01-21', 'transport',     'Rideshare home',         18.40),
  ('2026-01-24', 'dining',        'Dinner out',             52.00),
  ('2026-01-27', 'groceries',     'Weekly shop',            88.15),
  ('2026-01-30', 'utilities',     'Water bill',             31.25),
  ('2026-02-01', 'rent',          'February rent',        1450.00),
  ('2026-02-02', 'subscriptions', 'Streaming service',      15.99),
  ('2026-02-04', 'groceries',     'Corner market',          49.80),
  ('2026-02-06', 'travel',        'Weekend flights',       312.00),
  ('2026-02-07', 'dining',        'Airport food',           22.60),
  ('2026-02-10', 'groceries',     'Weekly shop',           102.55),
  ('2026-02-13', 'dining',        'Valentine dinner',       96.00),
  ('2026-02-15', 'subscriptions', 'Music service',           9.99),
  ('2026-02-17', 'transport',     'Metro card refill',      40.00),
  ('2026-02-20', 'groceries',     'Weekly shop',            79.90),
  ('2026-02-23', 'utilities',     'Electricity',            68.40),
  ('2026-02-26', 'dining',        'Takeout',                31.20);

SELECT id, spent_on, category, description, amount
FROM expenses
ORDER BY spent_on;
```

You should get 26 rows back, oldest first. Scan them. There's your rent
anchoring each month, a travel spike in early February, and groceries and dining
sprinkled throughout - enough variety that the analytics later will have
something to say.

## One thing to remember

Notice that this block did three jobs in one: `CREATE TABLE`, then `INSERT`,
then `SELECT`. **Every runnable block in the rest of this project repeats that
setup.** The same `CREATE TABLE` and the same 26-row `INSERT` will appear at the
top of every block before the new query.

That looks repetitive, and it is - on purpose. Each block runs in a fresh,
empty database, so it has to build its own world before it can query it. The
upside is that any block on any page works on its own: you can jump straight to
the window-functions phase, hit Run, and it works, because the data comes with
it.

So don't be thrown when you see the same long `INSERT` again. The only part
that changes from here on is the query at the bottom. That query is where the
report gets built.

## Try it

Before moving on, make one edit and re-run:

- Change a `dining` row's `amount` to something large, like `200.00`, and run
  again. Watch the total you'll compute next phase shift.
- Add a row in March (`'2026-03-...'`) and re-run. You've now got a third month,
  which the trend queries later will pick up automatically.

When the data feels like yours, head to phase 2 and start turning these rows
into totals.


---

# Totals and Groups

Twenty-six rows is a list, not a report. Nobody wants to read every expense to
find out you spent too much on dining. They want the number: dining, total.
That's what this phase builds - spend rolled up by category, then by month -
and the tool for both is `GROUP BY`.

## The mental shift: from rows to buckets

A plain `SELECT` gives you one output row per input row. `GROUP BY` changes the
deal: it sorts your rows into buckets that share a value, then hands you **one
output row per bucket**. The individual rows vanish into the bucket; what
survives is whatever you compute across them - a `SUM`, a `COUNT`, an `AVG`.

```mermaid
graph LR
  A[26 expense rows] --> B{GROUP BY category}
  B --> C[rent: 1 row]
  B --> D[groceries: 1 row]
  B --> E[dining: 1 row]
  B --> F[...]
```

The rule that trips everyone up: once you `GROUP BY category`, every column in
your `SELECT` must either be the thing you grouped by (`category`) or be wrapped
in an aggregate (`SUM(amount)`). You can't select `description`, because a
category bucket holds many descriptions and SQL wouldn't know which to show.

## Spend by category

Here's the first real report query. Which categories ate the most money?

**Your turn.** Write the query yourself. It should return, for each category:
`category`, `num_expenses` (a count of rows), and `total` (the sum of `amount`,
rounded to 2 decimals) - sorted with the biggest total first. Run it and check
the table against the rows listed below. My version is in the next block
whenever you want it.

```sql runnable
CREATE TABLE expenses (
  id          INTEGER PRIMARY KEY,
  spent_on    TEXT    NOT NULL,
  category    TEXT    NOT NULL,
  description TEXT    NOT NULL,
  amount      REAL    NOT NULL
);

INSERT INTO expenses (spent_on, category, description, amount) VALUES
  ('2026-01-01', 'rent',          'January rent',        1450.00),
  ('2026-01-02', 'subscriptions', 'Streaming service',      15.99),
  ('2026-01-03', 'groceries',     'Corner market',          54.20),
  ('2026-01-05', 'dining',        'Lunch with Sam',         28.75),
  ('2026-01-07', 'transport',     'Metro card refill',      40.00),
  ('2026-01-09', 'groceries',     'Weekly shop',            96.40),
  ('2026-01-12', 'utilities',     'Electricity',            72.10),
  ('2026-01-14', 'dining',        'Pizza night',            34.50),
  ('2026-01-16', 'subscriptions', 'Music service',           9.99),
  ('2026-01-18', 'groceries',     'Farmers market',         61.30),
  ('2026-01-21', 'transport',     'Rideshare home',         18.40),
  ('2026-01-24', 'dining',        'Dinner out',             52.00),
  ('2026-01-27', 'groceries',     'Weekly shop',            88.15),
  ('2026-01-30', 'utilities',     'Water bill',             31.25),
  ('2026-02-01', 'rent',          'February rent',        1450.00),
  ('2026-02-02', 'subscriptions', 'Streaming service',      15.99),
  ('2026-02-04', 'groceries',     'Corner market',          49.80),
  ('2026-02-06', 'travel',        'Weekend flights',       312.00),
  ('2026-02-07', 'dining',        'Airport food',           22.60),
  ('2026-02-10', 'groceries',     'Weekly shop',           102.55),
  ('2026-02-13', 'dining',        'Valentine dinner',       96.00),
  ('2026-02-15', 'subscriptions', 'Music service',           9.99),
  ('2026-02-17', 'transport',     'Metro card refill',      40.00),
  ('2026-02-20', 'groceries',     'Weekly shop',            79.90),
  ('2026-02-23', 'utilities',     'Electricity',            68.40),
  ('2026-02-26', 'dining',        'Takeout',                31.20);

-- Your turn: write a SELECT below that returns, for each category,
-- category / num_expenses (COUNT) / total (ROUND(SUM(amount), 2)),
-- sorted with the biggest total first.
```

Run it. You should get back exactly these 7 rows, biggest total first:

| category | num_expenses | total |
|---|---|---|
| rent | 2 | 2900 |
| groceries | 7 | 532.3 |
| travel | 1 | 312 |
| dining | 6 | 265.05 |
| utilities | 3 | 171.75 |
| transport | 3 | 98.4 |
| subscriptions | 4 | 51.96 |

If you left off the `SELECT`, running the block just prints "OK - 26 rows
affected." from the `INSERT` - that's your sign nothing queried the data yet.

Stuck? The mental-shift rule from above still applies: every column you select
has to be `category` itself or wrapped in an aggregate. And if the rows come
back right but alphabetical instead of biggest-first, that's `GROUP BY` doing
exactly what it promises - no particular order - and what's missing is the
`ORDER BY`.

### One way to write it

```sql runnable
CREATE TABLE expenses (
  id          INTEGER PRIMARY KEY,
  spent_on    TEXT    NOT NULL,
  category    TEXT    NOT NULL,
  description TEXT    NOT NULL,
  amount      REAL    NOT NULL
);

INSERT INTO expenses (spent_on, category, description, amount) VALUES
  ('2026-01-01', 'rent',          'January rent',        1450.00),
  ('2026-01-02', 'subscriptions', 'Streaming service',      15.99),
  ('2026-01-03', 'groceries',     'Corner market',          54.20),
  ('2026-01-05', 'dining',        'Lunch with Sam',         28.75),
  ('2026-01-07', 'transport',     'Metro card refill',      40.00),
  ('2026-01-09', 'groceries',     'Weekly shop',            96.40),
  ('2026-01-12', 'utilities',     'Electricity',            72.10),
  ('2026-01-14', 'dining',        'Pizza night',            34.50),
  ('2026-01-16', 'subscriptions', 'Music service',           9.99),
  ('2026-01-18', 'groceries',     'Farmers market',         61.30),
  ('2026-01-21', 'transport',     'Rideshare home',         18.40),
  ('2026-01-24', 'dining',        'Dinner out',             52.00),
  ('2026-01-27', 'groceries',     'Weekly shop',            88.15),
  ('2026-01-30', 'utilities',     'Water bill',             31.25),
  ('2026-02-01', 'rent',          'February rent',        1450.00),
  ('2026-02-02', 'subscriptions', 'Streaming service',      15.99),
  ('2026-02-04', 'groceries',     'Corner market',          49.80),
  ('2026-02-06', 'travel',        'Weekend flights',       312.00),
  ('2026-02-07', 'dining',        'Airport food',           22.60),
  ('2026-02-10', 'groceries',     'Weekly shop',           102.55),
  ('2026-02-13', 'dining',        'Valentine dinner',       96.00),
  ('2026-02-15', 'subscriptions', 'Music service',           9.99),
  ('2026-02-17', 'transport',     'Metro card refill',      40.00),
  ('2026-02-20', 'groceries',     'Weekly shop',            79.90),
  ('2026-02-23', 'utilities',     'Electricity',            68.40),
  ('2026-02-26', 'dining',        'Takeout',                31.20);

SELECT
  category,
  COUNT(*)            AS num_expenses,
  ROUND(SUM(amount), 2) AS total
FROM expenses
GROUP BY category
ORDER BY total DESC;
```

Read the output top to bottom. Rent dominates, as rent does. Groceries pile up
across many small trips - notice `num_expenses` is high there while each visit
is modest. Dining is the one to watch: lots of rows, and they add up.

Two things to notice in the query:

- `COUNT(*)` counts the rows in each bucket. It's how you tell "one big charge"
  from "death by a thousand cuts."
- `ROUND(SUM(amount), 2)` keeps the dollars to two decimal places. `SUM` over
  `REAL` can produce a trailing `.0000001`; `ROUND` tidies that up. This is the
  rounding caveat from phase 1, handled.

`ORDER BY total DESC` sorts the buckets biggest-first. `GROUP BY` doesn't
guarantee any order on its own, so if you want the worst offender at the top,
you say so.

## Spend by month

Categories tell you *what*. Months tell you *when*. To group by month you need
to turn each date into a month label, and SQLite's `strftime` does that:
`strftime('%Y-%m', spent_on)` turns `'2026-01-14'` into `'2026-01'`.

Group by that label and you get one row per month.

Before you run this, guess: which month comes out higher, January or February?

```sql runnable
CREATE TABLE expenses (
  id          INTEGER PRIMARY KEY,
  spent_on    TEXT    NOT NULL,
  category    TEXT    NOT NULL,
  description TEXT    NOT NULL,
  amount      REAL    NOT NULL
);

INSERT INTO expenses (spent_on, category, description, amount) VALUES
  ('2026-01-01', 'rent',          'January rent',        1450.00),
  ('2026-01-02', 'subscriptions', 'Streaming service',      15.99),
  ('2026-01-03', 'groceries',     'Corner market',          54.20),
  ('2026-01-05', 'dining',        'Lunch with Sam',         28.75),
  ('2026-01-07', 'transport',     'Metro card refill',      40.00),
  ('2026-01-09', 'groceries',     'Weekly shop',            96.40),
  ('2026-01-12', 'utilities',     'Electricity',            72.10),
  ('2026-01-14', 'dining',        'Pizza night',            34.50),
  ('2026-01-16', 'subscriptions', 'Music service',           9.99),
  ('2026-01-18', 'groceries',     'Farmers market',         61.30),
  ('2026-01-21', 'transport',     'Rideshare home',         18.40),
  ('2026-01-24', 'dining',        'Dinner out',             52.00),
  ('2026-01-27', 'groceries',     'Weekly shop',            88.15),
  ('2026-01-30', 'utilities',     'Water bill',             31.25),
  ('2026-02-01', 'rent',          'February rent',        1450.00),
  ('2026-02-02', 'subscriptions', 'Streaming service',      15.99),
  ('2026-02-04', 'groceries',     'Corner market',          49.80),
  ('2026-02-06', 'travel',        'Weekend flights',       312.00),
  ('2026-02-07', 'dining',        'Airport food',           22.60),
  ('2026-02-10', 'groceries',     'Weekly shop',           102.55),
  ('2026-02-13', 'dining',        'Valentine dinner',       96.00),
  ('2026-02-15', 'subscriptions', 'Music service',           9.99),
  ('2026-02-17', 'transport',     'Metro card refill',      40.00),
  ('2026-02-20', 'groceries',     'Weekly shop',            79.90),
  ('2026-02-23', 'utilities',     'Electricity',            68.40),
  ('2026-02-26', 'dining',        'Takeout',                31.20);

SELECT
  strftime('%Y-%m', spent_on) AS month,
  COUNT(*)                     AS num_expenses,
  ROUND(SUM(amount), 2)        AS total
FROM expenses
GROUP BY month
ORDER BY month;
```

Two rows, January and February. February runs higher - that travel splurge and
the Valentine dinner did their work. This is the bones of a trend, and in the
next phase you'll measure that month-to-month jump precisely instead of
squinting at it.

## Filtering buckets vs filtering rows

One more tool you'll want. `WHERE` filters rows *before* grouping. `HAVING`
filters buckets *after* grouping. They're not interchangeable - use the one that
matches what you're filtering on.

Say you only care about categories where you spent more than $150 total. That's
a filter on the bucket's `SUM`, so it's `HAVING`:

```sql runnable
CREATE TABLE expenses (
  id          INTEGER PRIMARY KEY,
  spent_on    TEXT    NOT NULL,
  category    TEXT    NOT NULL,
  description TEXT    NOT NULL,
  amount      REAL    NOT NULL
);

INSERT INTO expenses (spent_on, category, description, amount) VALUES
  ('2026-01-01', 'rent',          'January rent',        1450.00),
  ('2026-01-02', 'subscriptions', 'Streaming service',      15.99),
  ('2026-01-03', 'groceries',     'Corner market',          54.20),
  ('2026-01-05', 'dining',        'Lunch with Sam',         28.75),
  ('2026-01-07', 'transport',     'Metro card refill',      40.00),
  ('2026-01-09', 'groceries',     'Weekly shop',            96.40),
  ('2026-01-12', 'utilities',     'Electricity',            72.10),
  ('2026-01-14', 'dining',        'Pizza night',            34.50),
  ('2026-01-16', 'subscriptions', 'Music service',           9.99),
  ('2026-01-18', 'groceries',     'Farmers market',         61.30),
  ('2026-01-21', 'transport',     'Rideshare home',         18.40),
  ('2026-01-24', 'dining',        'Dinner out',             52.00),
  ('2026-01-27', 'groceries',     'Weekly shop',            88.15),
  ('2026-01-30', 'utilities',     'Water bill',             31.25),
  ('2026-02-01', 'rent',          'February rent',        1450.00),
  ('2026-02-02', 'subscriptions', 'Streaming service',      15.99),
  ('2026-02-04', 'groceries',     'Corner market',          49.80),
  ('2026-02-06', 'travel',        'Weekend flights',       312.00),
  ('2026-02-07', 'dining',        'Airport food',           22.60),
  ('2026-02-10', 'groceries',     'Weekly shop',           102.55),
  ('2026-02-13', 'dining',        'Valentine dinner',       96.00),
  ('2026-02-15', 'subscriptions', 'Music service',           9.99),
  ('2026-02-17', 'transport',     'Metro card refill',      40.00),
  ('2026-02-20', 'groceries',     'Weekly shop',            79.90),
  ('2026-02-23', 'utilities',     'Electricity',            68.40),
  ('2026-02-26', 'dining',        'Takeout',                31.20);

SELECT
  category,
  ROUND(SUM(amount), 2) AS total
FROM expenses
GROUP BY category
HAVING SUM(amount) > 150
ORDER BY total DESC;
```

The small categories - subscriptions, transport - drop off, leaving the ones
that actually move your budget.

You now have totals by category and by month. That's a report a person can read.
Next, we make it tell a story over time: a running total and how each month
compares to the one before - without losing the detail rows. That's what window
functions are for.


---

# Running Totals with Window Functions

`GROUP BY` is great at one thing and bad at another. Great: collapsing many rows
into one total. Bad: keeping the original rows *and* showing a total next to
each. The moment you want "this expense, and the running total up to and
including it," `GROUP BY` can't help - it already threw the individual rows away.

Window functions are the fix. They compute across a set of rows like an
aggregate does, but they hand the answer back **on every row** instead of
collapsing them. You keep your detail and get the rolling math too.

## The shape of a window function

A window function is an aggregate followed by `OVER (...)`. The `OVER` clause
defines the "window" - which rows this calculation looks at, and in what order.

```
SUM(amount) OVER (ORDER BY spent_on)
        ^                  ^
   the aggregate     the window: all rows up to this one, by date
```

Two pieces inside `OVER` matter most:

- `ORDER BY` - sets the order the window walks through rows. For a running
  total, that's chronological.
- `PARTITION BY` (optional) - splits the rows into independent groups, and the
  window resets at each group boundary. Think of it as "do this separately per
  category" or "per month."

If you've ever wanted a cumulative column in a spreadsheet - each cell adding
the one above - that's exactly a `SUM() OVER (ORDER BY ...)`.

## A running total of spending

Here's the cumulative spend over the whole period: each expense, plus the total
accumulated up to and including it. Run it.

```sql runnable
CREATE TABLE expenses (
  id          INTEGER PRIMARY KEY,
  spent_on    TEXT    NOT NULL,
  category    TEXT    NOT NULL,
  description TEXT    NOT NULL,
  amount      REAL    NOT NULL
);

INSERT INTO expenses (spent_on, category, description, amount) VALUES
  ('2026-01-01', 'rent',          'January rent',        1450.00),
  ('2026-01-02', 'subscriptions', 'Streaming service',      15.99),
  ('2026-01-03', 'groceries',     'Corner market',          54.20),
  ('2026-01-05', 'dining',        'Lunch with Sam',         28.75),
  ('2026-01-07', 'transport',     'Metro card refill',      40.00),
  ('2026-01-09', 'groceries',     'Weekly shop',            96.40),
  ('2026-01-12', 'utilities',     'Electricity',            72.10),
  ('2026-01-14', 'dining',        'Pizza night',            34.50),
  ('2026-01-16', 'subscriptions', 'Music service',           9.99),
  ('2026-01-18', 'groceries',     'Farmers market',         61.30),
  ('2026-01-21', 'transport',     'Rideshare home',         18.40),
  ('2026-01-24', 'dining',        'Dinner out',             52.00),
  ('2026-01-27', 'groceries',     'Weekly shop',            88.15),
  ('2026-01-30', 'utilities',     'Water bill',             31.25),
  ('2026-02-01', 'rent',          'February rent',        1450.00),
  ('2026-02-02', 'subscriptions', 'Streaming service',      15.99),
  ('2026-02-04', 'groceries',     'Corner market',          49.80),
  ('2026-02-06', 'travel',        'Weekend flights',       312.00),
  ('2026-02-07', 'dining',        'Airport food',           22.60),
  ('2026-02-10', 'groceries',     'Weekly shop',           102.55),
  ('2026-02-13', 'dining',        'Valentine dinner',       96.00),
  ('2026-02-15', 'subscriptions', 'Music service',           9.99),
  ('2026-02-17', 'transport',     'Metro card refill',      40.00),
  ('2026-02-20', 'groceries',     'Weekly shop',            79.90),
  ('2026-02-23', 'utilities',     'Electricity',            68.40),
  ('2026-02-26', 'dining',        'Takeout',                31.20);

SELECT
  spent_on,
  category,
  amount,
  ROUND(SUM(amount) OVER (ORDER BY spent_on, id), 2) AS running_total
FROM expenses
ORDER BY spent_on, id;
```

Read the `running_total` column down the page. It starts at the January rent and
climbs with every expense, ending at your grand total on the last row. Every
detail row is still there - that's the whole point. You'd never get this from
`GROUP BY`.

Two small but important details:

- We order by `spent_on, id`, not only `spent_on`. When two expenses share a
  date, `id` breaks the tie so the running total is deterministic. Order by a
  non-unique column alone and the cumulative value on tied rows can wobble.
- The `ORDER BY` inside `OVER` controls the math; the `ORDER BY` at the end
  controls how the result is displayed. Keep them aligned or the running total
  column will look scrambled even though it's correct.

## A running total that resets each month

Add `PARTITION BY` and the window restarts at each boundary. Here's the same
running total, but reset at the start of every month - useful for "how far into
this month's spending am I?"

Before you run it, guess: at which row does `month_running_total` drop back down?

```sql runnable
CREATE TABLE expenses (
  id          INTEGER PRIMARY KEY,
  spent_on    TEXT    NOT NULL,
  category    TEXT    NOT NULL,
  description TEXT    NOT NULL,
  amount      REAL    NOT NULL
);

INSERT INTO expenses (spent_on, category, description, amount) VALUES
  ('2026-01-01', 'rent',          'January rent',        1450.00),
  ('2026-01-02', 'subscriptions', 'Streaming service',      15.99),
  ('2026-01-03', 'groceries',     'Corner market',          54.20),
  ('2026-01-05', 'dining',        'Lunch with Sam',         28.75),
  ('2026-01-07', 'transport',     'Metro card refill',      40.00),
  ('2026-01-09', 'groceries',     'Weekly shop',            96.40),
  ('2026-01-12', 'utilities',     'Electricity',            72.10),
  ('2026-01-14', 'dining',        'Pizza night',            34.50),
  ('2026-01-16', 'subscriptions', 'Music service',           9.99),
  ('2026-01-18', 'groceries',     'Farmers market',         61.30),
  ('2026-01-21', 'transport',     'Rideshare home',         18.40),
  ('2026-01-24', 'dining',        'Dinner out',             52.00),
  ('2026-01-27', 'groceries',     'Weekly shop',            88.15),
  ('2026-01-30', 'utilities',     'Water bill',             31.25),
  ('2026-02-01', 'rent',          'February rent',        1450.00),
  ('2026-02-02', 'subscriptions', 'Streaming service',      15.99),
  ('2026-02-04', 'groceries',     'Corner market',          49.80),
  ('2026-02-06', 'travel',        'Weekend flights',       312.00),
  ('2026-02-07', 'dining',        'Airport food',           22.60),
  ('2026-02-10', 'groceries',     'Weekly shop',           102.55),
  ('2026-02-13', 'dining',        'Valentine dinner',       96.00),
  ('2026-02-15', 'subscriptions', 'Music service',           9.99),
  ('2026-02-17', 'transport',     'Metro card refill',      40.00),
  ('2026-02-20', 'groceries',     'Weekly shop',            79.90),
  ('2026-02-23', 'utilities',     'Electricity',            68.40),
  ('2026-02-26', 'dining',        'Takeout',                31.20);

SELECT
  spent_on,
  category,
  amount,
  ROUND(
    SUM(amount) OVER (
      PARTITION BY strftime('%Y-%m', spent_on)
      ORDER BY spent_on, id
    ), 2
  ) AS month_running_total
FROM expenses
ORDER BY spent_on, id;
```

Watch the `month_running_total` column: it climbs through January, then drops
back down at the first February row and climbs again. The `PARTITION BY` split
the data into a January window and a February window, each with its own
independent running total.

## Month-over-month change with LAG

Now the question every report wants to answer: did spending go up or down versus
last month, and by how much?

`LAG` is the tool. It reaches back to a previous row and pulls a value from it.
`LAG(total)` over months ordered by date means "the total from the row before
this one" - last month's number, sitting right next to this month's.

**Your turn.** Write a query that returns one row per month: `month`, `total`
(that month's spend, rounded to 2 decimals), `prev_month` (the previous row's
total, via `LAG`), and `change` (`total` minus `prev_month`, rounded). Run it
and compare against the rows listed below. My version is in the next block
whenever you want it.

```sql runnable
CREATE TABLE expenses (
  id          INTEGER PRIMARY KEY,
  spent_on    TEXT    NOT NULL,
  category    TEXT    NOT NULL,
  description TEXT    NOT NULL,
  amount      REAL    NOT NULL
);

INSERT INTO expenses (spent_on, category, description, amount) VALUES
  ('2026-01-01', 'rent',          'January rent',        1450.00),
  ('2026-01-02', 'subscriptions', 'Streaming service',      15.99),
  ('2026-01-03', 'groceries',     'Corner market',          54.20),
  ('2026-01-05', 'dining',        'Lunch with Sam',         28.75),
  ('2026-01-07', 'transport',     'Metro card refill',      40.00),
  ('2026-01-09', 'groceries',     'Weekly shop',            96.40),
  ('2026-01-12', 'utilities',     'Electricity',            72.10),
  ('2026-01-14', 'dining',        'Pizza night',            34.50),
  ('2026-01-16', 'subscriptions', 'Music service',           9.99),
  ('2026-01-18', 'groceries',     'Farmers market',         61.30),
  ('2026-01-21', 'transport',     'Rideshare home',         18.40),
  ('2026-01-24', 'dining',        'Dinner out',             52.00),
  ('2026-01-27', 'groceries',     'Weekly shop',            88.15),
  ('2026-01-30', 'utilities',     'Water bill',             31.25),
  ('2026-02-01', 'rent',          'February rent',        1450.00),
  ('2026-02-02', 'subscriptions', 'Streaming service',      15.99),
  ('2026-02-04', 'groceries',     'Corner market',          49.80),
  ('2026-02-06', 'travel',        'Weekend flights',       312.00),
  ('2026-02-07', 'dining',        'Airport food',           22.60),
  ('2026-02-10', 'groceries',     'Weekly shop',           102.55),
  ('2026-02-13', 'dining',        'Valentine dinner',       96.00),
  ('2026-02-15', 'subscriptions', 'Music service',           9.99),
  ('2026-02-17', 'transport',     'Metro card refill',      40.00),
  ('2026-02-20', 'groceries',     'Weekly shop',            79.90),
  ('2026-02-23', 'utilities',     'Electricity',            68.40),
  ('2026-02-26', 'dining',        'Takeout',                31.20);

-- Your turn: return one row per month with:
--   month       - 'YYYY-MM'
--   total       - that month's SUM(amount), rounded to 2 decimals
--   prev_month  - the previous row's total, via LAG
--   change      - total - prev_month, rounded to 2 decimals
-- January has no earlier month, so prev_month and change should come back empty there.
```

Run it. You should get back exactly these two rows:

| month | total | prev_month | change |
|---|---|---|---|
| 2026-01 | 2053.03 | NULL | NULL |
| 2026-02 | 2278.43 | 2053.03 | 225.4 |

`LAG` always needs an `OVER (...)`, even when you're not partitioning or
ordering by anything unusual. Drop it - `LAG(total) AS prev_month` on its own -
and SQLite refuses outright with `misuse of window function LAG()`. That's a
syntax problem, not a wrong number, so it's an easy one to fix once you see it.

Stuck on where `LAG` goes? Window functions run after `GROUP BY` collapses
rows, so you need a subquery (or CTE) that groups into monthly totals first,
with `LAG` running over *that* subquery's rows.

### One way to write it

```sql runnable
CREATE TABLE expenses (
  id          INTEGER PRIMARY KEY,
  spent_on    TEXT    NOT NULL,
  category    TEXT    NOT NULL,
  description TEXT    NOT NULL,
  amount      REAL    NOT NULL
);

INSERT INTO expenses (spent_on, category, description, amount) VALUES
  ('2026-01-01', 'rent',          'January rent',        1450.00),
  ('2026-01-02', 'subscriptions', 'Streaming service',      15.99),
  ('2026-01-03', 'groceries',     'Corner market',          54.20),
  ('2026-01-05', 'dining',        'Lunch with Sam',         28.75),
  ('2026-01-07', 'transport',     'Metro card refill',      40.00),
  ('2026-01-09', 'groceries',     'Weekly shop',            96.40),
  ('2026-01-12', 'utilities',     'Electricity',            72.10),
  ('2026-01-14', 'dining',        'Pizza night',            34.50),
  ('2026-01-16', 'subscriptions', 'Music service',           9.99),
  ('2026-01-18', 'groceries',     'Farmers market',         61.30),
  ('2026-01-21', 'transport',     'Rideshare home',         18.40),
  ('2026-01-24', 'dining',        'Dinner out',             52.00),
  ('2026-01-27', 'groceries',     'Weekly shop',            88.15),
  ('2026-01-30', 'utilities',     'Water bill',             31.25),
  ('2026-02-01', 'rent',          'February rent',        1450.00),
  ('2026-02-02', 'subscriptions', 'Streaming service',      15.99),
  ('2026-02-04', 'groceries',     'Corner market',          49.80),
  ('2026-02-06', 'travel',        'Weekend flights',       312.00),
  ('2026-02-07', 'dining',        'Airport food',           22.60),
  ('2026-02-10', 'groceries',     'Weekly shop',           102.55),
  ('2026-02-13', 'dining',        'Valentine dinner',       96.00),
  ('2026-02-15', 'subscriptions', 'Music service',           9.99),
  ('2026-02-17', 'transport',     'Metro card refill',      40.00),
  ('2026-02-20', 'groceries',     'Weekly shop',            79.90),
  ('2026-02-23', 'utilities',     'Electricity',            68.40),
  ('2026-02-26', 'dining',        'Takeout',                31.20);

SELECT
  month,
  total,
  LAG(total) OVER (ORDER BY month)                 AS prev_month,
  ROUND(total - LAG(total) OVER (ORDER BY month), 2) AS change
FROM (
  SELECT
    strftime('%Y-%m', spent_on) AS month,
    ROUND(SUM(amount), 2)        AS total
  FROM expenses
  GROUP BY month
) AS monthly
ORDER BY month;
```

The trick is doing it in two steps: first collapse to monthly totals with
`GROUP BY` (the subquery), then run `LAG` over those monthly rows. Window
functions operate after grouping, so the subquery gives them clean monthly rows
to walk.

January's `prev_month` is empty - there's no month before it, so `LAG` returns
nothing, and the subtraction is empty too. That's correct, not a bug: the first
row has nothing to compare against. February shows last month's total beside
this month's and the difference between them. Positive `change` means you spent
more; the February travel and dining pushed it up.

## What you can do now

You can keep every detail row and still answer "how much so far?" and "up or
down from last time?" - two questions `GROUP BY` alone can't touch. In the final
phase, you'll fold the category breakdown, the share-of-total, and this monthly
trend into one report query. Onward.


---

# The Final Report

You've built every piece: the table, the grouped totals, the running total, the
month-over-month change. This phase ties them into the two queries a real
expense report leads with - *where did the money go* (category share of total)
and *which way is it trending* (the monthly trend) - each as one self-contained
statement you could paste straight into a dashboard.

## Report part one: category share of total

A category total is useful. A category's **share of total** is the line that
ends arguments - "dining was 9% of everything" lands harder than a raw dollar
figure.

To get a percentage you need two numbers in the same row: each category's total,
and the grand total across all categories. The grand total is a single number
that has to be available on every row, and that's exactly a window function with
an empty `OVER ()` - no `ORDER BY`, no `PARTITION BY`, meaning "sum over the
whole result."

A CTE (the `WITH ... AS (...)` block) computes the per-category totals once, and
the outer query divides each by the windowed grand total.

Before you run it, guess: roughly what share of total spending is rent?

```sql runnable
CREATE TABLE expenses (
  id          INTEGER PRIMARY KEY,
  spent_on    TEXT    NOT NULL,
  category    TEXT    NOT NULL,
  description TEXT    NOT NULL,
  amount      REAL    NOT NULL
);

INSERT INTO expenses (spent_on, category, description, amount) VALUES
  ('2026-01-01', 'rent',          'January rent',        1450.00),
  ('2026-01-02', 'subscriptions', 'Streaming service',      15.99),
  ('2026-01-03', 'groceries',     'Corner market',          54.20),
  ('2026-01-05', 'dining',        'Lunch with Sam',         28.75),
  ('2026-01-07', 'transport',     'Metro card refill',      40.00),
  ('2026-01-09', 'groceries',     'Weekly shop',            96.40),
  ('2026-01-12', 'utilities',     'Electricity',            72.10),
  ('2026-01-14', 'dining',        'Pizza night',            34.50),
  ('2026-01-16', 'subscriptions', 'Music service',           9.99),
  ('2026-01-18', 'groceries',     'Farmers market',         61.30),
  ('2026-01-21', 'transport',     'Rideshare home',         18.40),
  ('2026-01-24', 'dining',        'Dinner out',             52.00),
  ('2026-01-27', 'groceries',     'Weekly shop',            88.15),
  ('2026-01-30', 'utilities',     'Water bill',             31.25),
  ('2026-02-01', 'rent',          'February rent',        1450.00),
  ('2026-02-02', 'subscriptions', 'Streaming service',      15.99),
  ('2026-02-04', 'groceries',     'Corner market',          49.80),
  ('2026-02-06', 'travel',        'Weekend flights',       312.00),
  ('2026-02-07', 'dining',        'Airport food',           22.60),
  ('2026-02-10', 'groceries',     'Weekly shop',           102.55),
  ('2026-02-13', 'dining',        'Valentine dinner',       96.00),
  ('2026-02-15', 'subscriptions', 'Music service',           9.99),
  ('2026-02-17', 'transport',     'Metro card refill',      40.00),
  ('2026-02-20', 'groceries',     'Weekly shop',            79.90),
  ('2026-02-23', 'utilities',     'Electricity',            68.40),
  ('2026-02-26', 'dining',        'Takeout',                31.20);

WITH by_category AS (
  SELECT category, SUM(amount) AS total
  FROM expenses
  GROUP BY category
)
SELECT
  category,
  ROUND(total, 2)                                   AS total,
  ROUND(100.0 * total / SUM(total) OVER (), 1)      AS pct_of_total
FROM by_category
ORDER BY total DESC;
```

Now you've got the headline. Rent is the biggest slice by far; the discretionary
categories - dining, travel - are the ones you can actually act on. The
`pct_of_total` column adds up to 100 across all rows, because the denominator is
the same grand total on every row. The `100.0 *` (not `100 *`) forces
floating-point division so you get `8.7`, not `0`.

## Report part two: the monthly trend

The second half of the report is the trend over time, with the running total and
the month-over-month change side by side - everything from phase 3, assembled
into one statement. This is the query you'd chart.

**Your turn.** Write it yourself: one row per month, with `month`, `total`
(that month's spend, rounded), `cumulative` (a running total across months, in
month order), and `mom_change` (this month minus last month, rounded). Run it
and compare against the rows below. My version is in the next block whenever
you want it.

```sql runnable
CREATE TABLE expenses (
  id          INTEGER PRIMARY KEY,
  spent_on    TEXT    NOT NULL,
  category    TEXT    NOT NULL,
  description TEXT    NOT NULL,
  amount      REAL    NOT NULL
);

INSERT INTO expenses (spent_on, category, description, amount) VALUES
  ('2026-01-01', 'rent',          'January rent',        1450.00),
  ('2026-01-02', 'subscriptions', 'Streaming service',      15.99),
  ('2026-01-03', 'groceries',     'Corner market',          54.20),
  ('2026-01-05', 'dining',        'Lunch with Sam',         28.75),
  ('2026-01-07', 'transport',     'Metro card refill',      40.00),
  ('2026-01-09', 'groceries',     'Weekly shop',            96.40),
  ('2026-01-12', 'utilities',     'Electricity',            72.10),
  ('2026-01-14', 'dining',        'Pizza night',            34.50),
  ('2026-01-16', 'subscriptions', 'Music service',           9.99),
  ('2026-01-18', 'groceries',     'Farmers market',         61.30),
  ('2026-01-21', 'transport',     'Rideshare home',         18.40),
  ('2026-01-24', 'dining',        'Dinner out',             52.00),
  ('2026-01-27', 'groceries',     'Weekly shop',            88.15),
  ('2026-01-30', 'utilities',     'Water bill',             31.25),
  ('2026-02-01', 'rent',          'February rent',        1450.00),
  ('2026-02-02', 'subscriptions', 'Streaming service',      15.99),
  ('2026-02-04', 'groceries',     'Corner market',          49.80),
  ('2026-02-06', 'travel',        'Weekend flights',       312.00),
  ('2026-02-07', 'dining',        'Airport food',           22.60),
  ('2026-02-10', 'groceries',     'Weekly shop',           102.55),
  ('2026-02-13', 'dining',        'Valentine dinner',       96.00),
  ('2026-02-15', 'subscriptions', 'Music service',           9.99),
  ('2026-02-17', 'transport',     'Metro card refill',      40.00),
  ('2026-02-20', 'groceries',     'Weekly shop',            79.90),
  ('2026-02-23', 'utilities',     'Electricity',            68.40),
  ('2026-02-26', 'dining',        'Takeout',                31.20);

-- Your turn: return one row per month with:
--   month       - 'YYYY-MM'
--   total       - that month's SUM(amount), rounded to 2 decimals
--   cumulative  - the running total across months, in month order
--   mom_change  - total minus the previous month's total, rounded
-- Same shape as phase 3's LAG query, plus a running-total column.
```

Run it. You should get back exactly these two rows:

| month | total | cumulative | mom_change |
|---|---|---|---|
| 2026-01 | 2053.03 | 2053.03 | NULL |
| 2026-02 | 2278.43 | 4331.46 | 225.4 |

If your query comes back with 26 rows instead of 2, you skipped the `GROUP BY`
step - the window functions are running over individual expenses instead of
over monthly totals. Collapse to months in a CTE first, same as phase 3, then
run both window functions over that CTE's rows.

### One way to write it

```sql runnable
CREATE TABLE expenses (
  id          INTEGER PRIMARY KEY,
  spent_on    TEXT    NOT NULL,
  category    TEXT    NOT NULL,
  description TEXT    NOT NULL,
  amount      REAL    NOT NULL
);

INSERT INTO expenses (spent_on, category, description, amount) VALUES
  ('2026-01-01', 'rent',          'January rent',        1450.00),
  ('2026-01-02', 'subscriptions', 'Streaming service',      15.99),
  ('2026-01-03', 'groceries',     'Corner market',          54.20),
  ('2026-01-05', 'dining',        'Lunch with Sam',         28.75),
  ('2026-01-07', 'transport',     'Metro card refill',      40.00),
  ('2026-01-09', 'groceries',     'Weekly shop',            96.40),
  ('2026-01-12', 'utilities',     'Electricity',            72.10),
  ('2026-01-14', 'dining',        'Pizza night',            34.50),
  ('2026-01-16', 'subscriptions', 'Music service',           9.99),
  ('2026-01-18', 'groceries',     'Farmers market',         61.30),
  ('2026-01-21', 'transport',     'Rideshare home',         18.40),
  ('2026-01-24', 'dining',        'Dinner out',             52.00),
  ('2026-01-27', 'groceries',     'Weekly shop',            88.15),
  ('2026-01-30', 'utilities',     'Water bill',             31.25),
  ('2026-02-01', 'rent',          'February rent',        1450.00),
  ('2026-02-02', 'subscriptions', 'Streaming service',      15.99),
  ('2026-02-04', 'groceries',     'Corner market',          49.80),
  ('2026-02-06', 'travel',        'Weekend flights',       312.00),
  ('2026-02-07', 'dining',        'Airport food',           22.60),
  ('2026-02-10', 'groceries',     'Weekly shop',           102.55),
  ('2026-02-13', 'dining',        'Valentine dinner',       96.00),
  ('2026-02-15', 'subscriptions', 'Music service',           9.99),
  ('2026-02-17', 'transport',     'Metro card refill',      40.00),
  ('2026-02-20', 'groceries',     'Weekly shop',            79.90),
  ('2026-02-23', 'utilities',     'Electricity',            68.40),
  ('2026-02-26', 'dining',        'Takeout',                31.20);

WITH by_month AS (
  SELECT
    strftime('%Y-%m', spent_on) AS month,
    SUM(amount)                  AS total
  FROM expenses
  GROUP BY month
)
SELECT
  month,
  ROUND(total, 2)                                          AS total,
  ROUND(SUM(total) OVER (ORDER BY month), 2)               AS cumulative,
  ROUND(total - LAG(total) OVER (ORDER BY month), 2)       AS mom_change
FROM by_month
ORDER BY month;
```

One table, the full trend: each month's spend, the cumulative total across all
months, and how each month moved versus the last. January's `mom_change` is
empty (no prior month), `cumulative` ends at the grand total. This is a report -
the kind you'd refresh monthly and glance at to know whether you're drifting.

## You built a report

Step back and look at what you have. From a flat table of 26 expenses, in four
short phases, you produced:

| You learned | You used it for |
|-------------|-----------------|
| `GROUP BY` + `SUM` | Category and monthly totals |
| `HAVING` | Filtering to categories that matter |
| `SUM() OVER` | Running totals, with and without partitions |
| `LAG` | Month-over-month change |
| `OVER ()` + CTEs | Share-of-total in one query |

Those five techniques cover the vast majority of analytical SQL you'll ever
write. Sales, signups, errors per day - it's the same shapes with different
column names.

## Extend it

The data is yours - keep going:

- **Add March.** Insert a handful of `'2026-03-...'` rows and re-run the trend
  query. The trend extends with no code change - that's the payoff of writing it
  against the data instead of hardcoding two months.
- **Category trend.** Combine the techniques: `GROUP BY month, category`, then
  `PARTITION BY category ORDER BY month` to get each category's own
  month-over-month change. Which category is creeping up fastest?
- **Biggest expense per month.** Use `RANK() OVER (PARTITION BY month ORDER BY
  amount DESC)` and keep the rows where rank = 1.
- **A budget column.** Add a `budgets` table (`category`, `monthly_limit`), join
  it to your monthly category totals, and flag where you went over.

Each of those is a small variation on what you've already done. Pick one, open
any block above, swap the bottom query, and run it. You've got the loop now -
edit, run, read the output, repeat. That's how SQL stops being syntax and starts
being a tool you reach for.