1.GreenMart vs. Amazon, and a Bank

This is the exact shape of almost every report GreenMart has written since Act 3: clean, separate tables, joined together whenever someone asks a question. It gives the right answer every time. But look closely at what it actually does — every single time someone runs this report, SQLite has to redo the same join and the same grouping work again, from nothing, even if the last person asked the exact same question five minutes ago.

SELECT p.Name, SUM(oi.Quantity) AS UnitsSold, SUM(oi.Quantity * p.Price) AS Revenue
FROM OrderItems oi
JOIN Products p ON p.ProductID = oi.ProductID
JOIN Orders o ON o.OrderID = oi.OrderID
GROUP BY p.Name
ORDER BY Revenue DESC;

EXPLAIN QUERY PLAN shows the real cost hiding behind this simple-looking query: one scan, two searches, and two separate sorting steps.

A system built for heavy reporting — the kind a massive e-commerce platform runs nonstop, all day — usually doesn't wait for someone to ask a question before doing the work. Instead, it computes the answer once, on a schedule, and saves it into its own table. In a real data warehouse, a table like this is called a fact table, and this whole style of building several ready-made tables for reporting is often called a star schema. It's the same CREATE TABLE ... AS SELECT trick from Act 3's materialized-view chapter — just used as a real, deliberate design choice this time, not a one-off shortcut.

CREATE TABLE ProductSalesSummary AS
SELECT p.Name, SUM(oi.Quantity) AS UnitsSold, SUM(oi.Quantity * p.Price) AS Revenue
FROM OrderItems oi
JOIN Products p ON p.ProductID = oi.ProductID
JOIN Orders o ON o.OrderID = oi.OrderID
GROUP BY p.Name;

ProductSalesSummary now holds the finished answer, sitting there, ready to be read directly.

Same question. Same answer. But now there's only one table, and nothing left to join.

SELECT * FROM ProductSalesSummary ORDER BY Revenue DESC;

EXPLAIN QUERY PLAN drops from a scan plus two searches plus two sorting steps, down to just one scan and one sorting step. The cost of this speed: ProductSalesSummary is only as fresh as the last time someone rebuilt it — unlike the live join, which always reflects right now.

This is how GreenMart has tracked money since Act 1: one column, changed directly whenever it needs to update. It's simple, and for a small store, that simplicity is genuinely fine.

UPDATE Customers SET BalanceDue = BalanceDue - 40 WHERE CustomerID = 101;
SELECT * FROM Customers WHERE CustomerID = 101;

The instant this UPDATE runs, the fact that BalanceDue was ever 140 is gone. Nowhere in the database does it say that number used to be anything else.

A real bank almost never stores a balance as one number that gets changed. Instead, every single movement of money — every deposit, every payment, every charge — becomes its own permanent row that never gets touched again. The balance itself isn't stored anywhere. It's worked out fresh, every time, by adding up every row that ever happened. This style has a name in the software world too: event sourcing, or simply an append-only ledger.

CREATE TABLE AccountLedger (
  EntryID INTEGER PRIMARY KEY,
  CustomerID INTEGER NOT NULL REFERENCES Customers(CustomerID),
  Amount NUMERIC NOT NULL,
  EntryDate DATE NOT NULL,
  Description TEXT NOT NULL
);

INSERT INTO AccountLedger (EntryID, CustomerID, Amount, EntryDate, Description) VALUES
  (1, 101, 140, '2026-07-01', 'Opening balance'),
  (2, 101, -40, '2026-08-20', 'Payment received');

SELECT CustomerID, SUM(Amount) AS CurrentBalance FROM AccountLedger
WHERE CustomerID = 101
GROUP BY CustomerID;

CurrentBalance comes back as 100 — the exact same number GreenMart's UPDATE gave. But here, every single step that led to that number is still sitting right there in the table, forever.

Suppose someone made a mistake — say, the same payment got typed in twice by accident. In a ledger, nobody goes back and deletes the wrong row, or quietly edits it like it never happened. Instead, someone adds a brand new row that cancels the mistake out. The wrong entry stays visible forever, right next to the entry that fixes it.

INSERT INTO AccountLedger (EntryID, CustomerID, Amount, EntryDate, Description) VALUES
  (3, 101, -40, '2026-08-21', 'Correction: payment recorded twice by mistake'),
  (4, 101, 40, '2026-08-21', 'Reversal of duplicate payment entry');

SELECT CustomerID, SUM(Amount) AS CurrentBalance FROM AccountLedger
WHERE CustomerID = 101
GROUP BY CustomerID;

CurrentBalance is still exactly 100 — correct. And now the ledger honestly shows that a mistake happened and was caught, instead of pretending it never did.

Here's something GreenMart's own mutable BalanceDue column can never do: answer what the balance used to be on some earlier date. A mutable column only ever holds one value — right now. The moment it changes, whatever it held before is gone for good. A ledger never loses that — asking "what was true on August 20th" is just a matter of adding up only the rows that existed by then.

INSERT INTO AccountLedger (EntryID, CustomerID, Amount, EntryDate, Description) VALUES
  (5, 101, 200, '2026-08-25', 'New order placed on credit');

SELECT SUM(Amount) AS BalanceOnAug20 FROM AccountLedger
WHERE CustomerID = 101 AND EntryDate <= '2026-08-20';

SELECT SUM(Amount) AS BalanceToday FROM AccountLedger
WHERE CustomerID = 101;

BalanceOnAug20 comes back as 100 — exactly what the balance really was on that date. BalanceToday comes back as 300, after one more real charge landed on the 25th. Both answers sit in the exact same table, at the exact same time, because nothing was ever thrown away.