MariaDB Tutorial / Calculate Running Totals and Compare Rows with LAG and LEAD

A windowed SUM calculates a running total without collapsing the input rows, while LAG and LEAD read values from preceding and following rows in the chosen order. Specify a deterministic order and the intended accumulation frame so equal timestamps do not leave the calculation ambiguous.

Define the time sequence and the row being accumulated

An order-by-order cumulative amount differs from a daily cumulative amount. This example first sums purchase-time quantity × unit_price for each paid order, then accumulates by order timestamp and ID. Shipping, cancellations and refunds require separate policies; this measure includes only the defined paid goods. Use Compare ROW_NUMBER, RANK, and DENSE_RANK to distinguish row-preserving calculations from grouping, and Calculate Date Intervals, Differences, and Display Formats to separate a previous row from an elapsed date interval.

Check the MariaDB documentation for the syntax and conditions used here: LAG, window functions overview.

Prepare a deterministic sequence of orders

Connect to the learning server as course_user and select shopcourse. The command-line and UTF-8 SQL-file guide explains how to save the code as a UTF-8 file and execute it with SOURCE C:/mariadb-lab/lesson.sql;, replacing the path with the actual file. Run SQL inside the MariaDB client, rather than at the operating-system prompt.

Check SELECT VERSION(), DATABASE(); and SHOW TABLES; before creating c33_customers, c33_products, c33_orders, c33_items. These names belong only to this exercise. If they exist, inspect their definitions and rows first; remove them only when you have established that they are disposable exercise objects. Do not replace existing application tables.

USE shopcourse;
CREATE TABLE c33_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c33_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c33_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c33_order_customer FOREIGN KEY (customer_id)
  REFERENCES c33_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c33_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c33_item_order FOREIGN KEY (order_id)
  REFERENCES c33_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c33_item_product FOREIGN KEY (product_id)
  REFERENCES c33_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c33_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c33_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c33_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c33_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

The SQL retains Korean sample names and city strings, while the explanations use stable customer and product IDs. Products 1–4 are a notebook, pen, mug and lamp. Customers 1 and 3 have the Seoul city value; customers 2 and 4 have Busan and Daejeon values. Preserve the supplied UTF-8 literals when reproducing the results.

The paid sequence is order 101 on January 10, 102 on January 20 and 103 on February 5. order_id provides a deterministic secondary order when timestamps tie. This use does not claim that identifiers universally encode chronological order.

Use an explicit ROWS frame for accumulation

WITH order_totals AS (
 SELECT o.order_id,o.ordered_at,SUM(i.quantity*i.unit_price) AS goods_total
 FROM c33_orders o JOIN c33_items i ON i.order_id=o.order_id
 WHERE o.status='paid' GROUP BY o.order_id,o.ordered_at
)
SELECT order_id,goods_total,
 SUM(goods_total) OVER(ORDER BY ordered_at,order_id
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
 LAG(goods_total) OVER(ORDER BY ordered_at,order_id) AS previous_total,
 LEAD(goods_total) OVER(ORDER BY ordered_at,order_id) AS next_total
FROM order_totals ORDER BY ordered_at,order_id;

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explicitly includes the ordered first row through the current row. Do not rely on an omitted default frame to express an intended row-by-row accumulation. ROWS and RANGE can treat peers differently when ordering values tie; the unique secondary key here also makes the sequence explicit.

Use this self-contained query to inspect peers without changing any table. Orders 501 and 502 share a timestamp but have different amounts.

WITH sample AS (
 SELECT 501 AS order_id,CAST('2026-01-10 10:00:00' AS DATETIME) AS ordered_at,10 AS amount
 UNION ALL SELECT 502,'2026-01-10 10:00:00',20
 UNION ALL SELECT 503,'2026-01-11 10:00:00',5
)
SELECT order_id,amount,
 SUM(amount) OVER (ORDER BY ordered_at,order_id
   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_unique,
 SUM(amount) OVER (ORDER BY ordered_at,order_id
   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_unique,
 SUM(amount) OVER (ORDER BY ordered_at
   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_time
FROM sample ORDER BY ordered_at,order_id;

The first two columns use the same unique timestamp-and-ID order and differ only in ROWS versus RANGE; both therefore produce 10, 30, 35. The third removes only the ID from RANGE ordering, so timestamp peers accumulate together as 30, 30, 35. ROWS without a unique tie-breaker would not define the order inside the timestamp tie.

Interpret neighboring rows rather than missing calendar days

LAG means a prior row in the selected ordering, not necessarily the previous calendar day. It does not create rows for dates without orders. A day-over-day metric needs a daily aggregation and a deliberate missing-date policy.

Subtracting the previous order amount yields a difference between neighboring orders; the first result is NULL because there is no predecessor. Replacing it with zero defines a baseline rather than merely prettifies the display. Missing observations and measured zero values are different information.

Choose whether filtering happens before or after accumulation

Filtering to February before computing the window starts the total within February. To display February rows with a total accumulated from earlier months, finish the window calculation inside a CTE and apply the display-date filter outside it. Both are valid queries but represent different starting points.

If details are not summarized first, the running total advances per item line rather than per order. Record the base grain, qualifying statuses, sort keys, frame and filter stage when defining a cumulative metric.

The first query accumulates all paid orders and then displays February. The second moves the same date predicate before the window calculation. Both use the existing c33 tables without modifying data.

WITH order_totals AS (
SELECT o.order_id,o.ordered_at,SUM(i.quantity*i.unit_price) AS goods_total
FROM c33_orders o JOIN c33_items i ON i.order_id=o.order_id
WHERE o.status='paid' GROUP BY o.order_id,o.ordered_at
), running AS (
 SELECT order_id,ordered_at,goods_total,
        SUM(goods_total) OVER (ORDER BY ordered_at,order_id
          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
 FROM order_totals
)
SELECT order_id,goods_total,running_total FROM running
WHERE ordered_at >= '2026-02-01' AND ordered_at < '2026-03-01'
ORDER BY ordered_at,order_id;

WITH order_totals AS (
SELECT o.order_id,o.ordered_at,SUM(i.quantity*i.unit_price) AS goods_total
FROM c33_orders o JOIN c33_items i ON i.order_id=o.order_id
WHERE o.status='paid' GROUP BY o.order_id,o.ordered_at
)
SELECT order_id,goods_total,
       SUM(goods_total) OVER (ORDER BY ordered_at,order_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM order_totals
WHERE ordered_at >= '2026-02-01' AND ordered_at < '2026-03-01'
ORDER BY ordered_at,order_id;

Both results contain order 103, but their accumulation boundaries differ. Decide whether the report needs an all-period running total or one that restarts inside the displayed month.

Verify the first, last and tied-order cases

Reconcile the three cumulative amounts by hand: 11500, 19500 and 39000. Check NULL at the first LAG and final LEAD boundaries. In a separate controlled trial, a same-time order should still have a defined position through the secondary key. Keep the final ORDER BY explicit rather than depend on an index’s incidental sequence.

Before repeating the exercise, inspect the selected database, actual table definitions and stored rows, and back up anything that must be kept. Drop only this article’s disposable practice objects, with child tables before their referenced parents. DROP TABLE removes both definitions and rows and cannot be undone by an ordinary ROLLBACK; keep cleanup separate from transaction experiments.

DROP TABLE shopcourse.c33_items;
DROP TABLE shopcourse.c33_orders;
DROP TABLE shopcourse.c33_products;
DROP TABLE shopcourse.c33_customers;