Archive Closed Orders

An online marketplace keeps its live orders small by moving old, finished orders into shop_orders_archive (same columns).

Move every order that was placed before 1 January 2026 (that is, any time up to 2025-12-31 23:59:59) and is closed, meaning its status is 'delivered' or 'cancelled'. Each moved order must be copied unchanged into shop_orders_archive and removed from shop_orders. Open orders ('pending', 'shipped') stay in shop_orders however old they are, and rows already in the archive stay there.

Do not return anything. The judge then lists both tables as tbl, order_id, buyer_id, placed_at, total_cents, status, where tbl is 'archive' or 'live', sorted by tbl, order_id.

Tables

shop_orders — Live orders, one row per order.

shop_orders_archive — Archived orders, same layout as shop_orders. May already contain rows from earlier runs.

Example

shop_orders

shop_orders_archive

Expected output

Return the rows in exactly the order described. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Changing Data · problem 61 of 70 · SQLite

The idea

SQL has no single "move" command, so the job is a copy followed by a delete over the same set of rows.

Pick the rows once, precisely. The cut-off is the start of 2026, so the clean test is placed_at < '2026-01-01'. ISO timestamps compare correctly as text, and every moment of 31 December is smaller than '2026-01-01', while '2026-01-01 00:00:00' is not. Writing <= '2025-12-31' looks equivalent but fails for any order with a time later than midnight on that day. Add status IN ('delivered', 'cancelled') and wrap the OR in parentheses (or use IN) so it does not escape the date filter.

Copy, then delete. INSERT INTO shop_orders_archive SELECT ... copies the rows unchanged, then DELETE with the identical predicate removes them. The alternative deletes by id from the archive, which ties the delete directly to what was copied. In production you would run both inside one transaction so a failure cannot leave rows half-moved.

Solution

INSERT INTO shop_orders_archive (order_id, buyer_id, placed_at, total_cents, status)
SELECT order_id, buyer_id, placed_at, total_cents, status
FROM shop_orders
WHERE placed_at < '2026-01-01'
  AND status IN ('delivered', 'cancelled');

DELETE FROM shop_orders
WHERE placed_at < '2026-01-01'
  AND status IN ('delivered', 'cancelled');

Another way

INSERT INTO shop_orders_archive
SELECT *
FROM shop_orders
WHERE date(placed_at) <= '2025-12-31'
  AND status IN ('delivered', 'cancelled');

-- delete exactly the rows that now exist in the archive
DELETE FROM shop_orders
WHERE order_id IN (SELECT order_id FROM shop_orders_archive);

Why common answers fail

Compares a timestamp to a bare date with <=, so orders placed during 31 December ('2025-12-31 18:40:00' > '2025-12-31') are left behind.

INSERT INTO shop_orders_archive
SELECT * FROM shop_orders
WHERE placed_at <= '2025-12-31' AND status IN ('delivered', 'cancelled');
DELETE FROM shop_orders
WHERE placed_at <= '2025-12-31' AND status IN ('delivered', 'cancelled');

The DELETE drops the status filter, so old open orders vanish from the live table without ever reaching the archive.

INSERT INTO shop_orders_archive
SELECT * FROM shop_orders
WHERE placed_at < '2026-01-01' AND status IN ('delivered', 'cancelled');
DELETE FROM shop_orders
WHERE placed_at < '2026-01-01';

Copies the rows but never deletes them, so every archived order now exists twice.

INSERT INTO shop_orders_archive
SELECT * FROM shop_orders
WHERE placed_at < '2026-01-01' AND status IN ('delivered', 'cancelled');

AND binds tighter than OR, so every cancelled order is moved regardless of its date.

INSERT INTO shop_orders_archive
SELECT * FROM shop_orders
WHERE placed_at < '2026-01-01' AND status = 'delivered' OR status = 'cancelled';
DELETE FROM shop_orders
WHERE placed_at < '2026-01-01' AND status = 'delivered' OR status = 'cancelled';

What the interviewer is checking

Tests whether you keep the INSERT and DELETE predicates identical and handle timestamp boundaries correctly, which is where real archive jobs lose or duplicate data.

Mistakes to avoid

• Using <= '2025-12-31' on a datetime column and missing orders placed later that day.
• Different WHERE clauses in the INSERT and the DELETE, which loses or duplicates rows.
• Mixing AND with OR without parentheses.
• Forgetting the DELETE, leaving every archived order in both tables.

Follow-ups to expect

• Make the move atomic (BEGIN; ...; COMMIT;).
• Archive anything older than 90 days relative to today (placed_at < date('now', '-90 days')).
• Archive in batches of 1,000 rows to avoid long locks (LIMIT on the selected ids and loop).

In real work

Data-retention jobs move closed orders, old logs or resolved tickets into cold tables every night to keep the hot tables small and fast.

Practice next