Example 1
Show the cumulative number of signups by date
Each row counts all rows from the start of the ordered result through the current row. The id tiebreaker makes the two Jan 3 rows deterministic.
Source table data Rows loaded before the example query runs.
Setup
CREATE TABLE signups (id INT, signup_date VARCHAR(10));
INSERT INTO
signups
VALUES
(1, '2024-01-01'),
(2, '2024-01-03'),
(3, '2024-01-03'),
(4, '2024-01-05');Validated query Shared across supported engines.
SQL
SELECT
id,
signup_date,
COUNT(*) OVER (
ORDER BY
signup_date,
id ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS running_count
FROM
signups
ORDER BY
signup_date,
id;Expected result Returned rows for the shared example.
| id | signup_date | running_count |
|---|---|---|
| 1 | 2024-01-01 | 1 |
| 2 | 2024-01-03 | 2 |
| 3 | 2024-01-03 | 3 |
| 4 | 2024-01-05 | 4 |
The explicit ROWS frame makes ties deterministic with the id tiebreaker.