The bakery, stage 6: the report, the copy and DROP in SQL

Problem

The month is over

October is over. The bakery wants a report: how the revenue grows day by day, who the best customer is and what share of the money comes from them. Then it wants a copy of the database before a new season starts. And you, to see the whole road, will take the database apart piece by piece until it is empty, as at the start of stage 1.

The fields start from the database after stage 5: the data, the index, the view order_totals, the trigger take_stock and the column phone.

The idea

The running total

A window function works out a value from several rows without merging them into one. SUM(t.total) OVER (ORDER BY o.ordered_at) is the sum of all the orders up to and including this row:

SQLempty database

SELECT o.ordered_at, t.total,
       SUM(t.total) OVER (ORDER BY o.ordered_at) AS so_far
FROM order_totals t
JOIN orders o ON o.id = t.id
WHERE t.status <> 'cancelled'
ORDER BY o.ordered_at;

That way you see how the month grows to €47, without the cancelled order.

Try it

The report

A report by customer, without the cancelled orders: the name, the amount spent (spent), the share of all revenue in percent (share, rounded to 1 place) and the place in the ranking (place, with RANK). Order by place. Take the sums from the view order_totals.

SQLempty database

SELECT c.name, SUM(t.total) AS spent
FROM order_totals t
JOIN customers c ON c.id = t.customer_id
GROUP BY c.id;

Solution

SELECT c.name,
       SUM(t.total) AS spent,
       ROUND(100.0 * SUM(t.total) / SUM(SUM(t.total)) OVER (), 1) AS share,
       RANK() OVER (ORDER BY SUM(t.total) DESC) AS place
FROM order_totals t
JOIN customers c ON c.id = t.customer_id
WHERE t.status <> 'cancelled'
GROUP BY c.id
ORDER BY place;

More precisely, if you are curious

RANK gives the same place to equals and skips the next one (1, 1, 3). DENSE_RANK does not skip (1, 1, 2), and ROW_NUMBER gives different numbers even to equals.

The idea

The copy

Before a big change (a new season, a migration, an import of data) you make a copy. VACUUM INTO writes the whole database to a new file while it is running, and ATTACH opens the copy as a second database next to the first:

SQLempty database

VACUUM INTO '/bakery-2026-10-31.db';
ATTACH '/bakery-2026-10-31.db' AS copy;

SELECT
  (SELECT COUNT(*) FROM orders) AS orders_now,
  (SELECT COUNT(*) FROM copy.orders) AS orders_in_copy,
  (SELECT COUNT(*) FROM copy.sqlite_master WHERE type = 'table') AS tables_in_copy;

The six orders and the five tables are in the copy too.

Run the box a second time and VACUUM INTO stops: the file already exists. It is the same in real work: every copy is a new file with the date in its name.

More precisely, if you are curious

In PostgreSQL the copy is made from outside: pg_dump bakery > bakery-2026-10-31.sql and restored with psql. A copy that nobody has tried to restore is not a copy.

See it

What depends on what

The view order_totals reads from order_items. If the table goes first:

SQLempty database

DROP TABLE order_items;
SELECT * FROM order_totals;

The view stays, but now points to nothing. The indexes and triggers of a table disappear with it, views do not. That is why the views are removed first. PostgreSQL is stricter: there DROP TABLE stops while a view reads from the table.

Try it

The view, the trigger and the index

Remove the view order_totals, the trigger take_stock and the index orders_customer.

SQLempty database

SELECT type, name
FROM sqlite_master
WHERE type IN ('view', 'trigger', 'index') AND name NOT LIKE 'sqlite_%';

Solution

DROP VIEW order_totals;
DROP TRIGGER take_stock;
DROP INDEX orders_customer;

See it

The parent first

A table that others point to cannot disappear while there are rows pointing to it:

SQLempty database

DROP TABLE customers;

DROP TABLE first deletes all the rows, and the foreign key in orders does not let customers with orders disappear.

Exercise

An empty database

Remove the five tables so that no DROP stops with an error. At the end nothing may be left in the database.

SQLempty database

DROP TABLE order_items;

Solution

DROP TABLE order_items;
DROP TABLE orders;
DROP TABLE products;
DROP TABLE customers;
DROP TABLE categories;

More precisely, if you are curious

orders before order_items also works, but by chance: ON DELETE CASCADE deletes the rows of the orders before the table disappears. Without CASCADE the same order stops. The safe order is always from the children to the parents. In PostgreSQL DROP TABLE … CASCADE also removes everything that depends on the table: handy and dangerous.

The idea

The whole road

The database is empty, as at the start. Along the way:

Stage 1: the five CREATE TABLEs with keys, NOT NULL, UNIQUE, CHECK, DEFAULT and CASCADE.
Stage 2: INSERT, RETURNING, a transaction with BEGIN and COMMIT, ROLLBACK and INSERT … SELECT.
Stage 3: the questions with JOIN, LEFT JOIN, GROUP BY, WITH and COALESCE.
Stage 4: UPDATE, DELETE and upsert, with the rules that stop the wrong ones.
Stage 5: an index, a view, a trigger and ALTER TABLE.
Stage 6: a report with windows, a copy with VACUUM INTO and DROP.

On your computer the last step is deleting the file bakery.db. In PostgreSQL it is DROP DATABASE bakery;, run from another database while nobody is connected to it.

Test

Check yourself

1. What is the difference between SUM(t.total) with GROUP BY and SUM(SUM(t.total)) OVER ()?The second is an errorThe first is the sum of the group, the second is the total of all groups, shown on every rowThere is no difference

2. Why does DROP TABLE customers stop with an error while there are orders?DROP needs IF EXISTScustomers is a protected tableorders points to customers. DROP first deletes the rows, and the foreign key does not let customers with orders disappear

3. You delete the table order_items. What happens to the view order_totals?It keeps the old sumsIt stays, but every read from it gives an errorIt is deleted with it

The bakery, stage 6: the report, the copy and DROP

Cookies