Bonus points and loyalty cards are a classic case where application code accumulates business rules that a database can express directly. Given a table of awarded points per card, several questions can be answered with windowing functions and frame clauses instead.
Schema and sample data
A minimal model stores one row per award event, tied to a card and a timestamp:
|
1 2 3 4 5 6 |
CREATE TABLE t_bonus_card ( card_number text NOT NULL, d date, points int ); |
Sample rows for two bonus cards:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
COPY t_bonus_card FROM stdin DELIMITER ';'; A4711;2022-01-01;8 A4711;2022-01-04;7 A4711;2022-02-12;3 A4711;2022-05-05;2 A4711;2022-06-07;9 A4711;2023-02-02;4 A4711;2023-03-03;7 A4711;2023-05-02;1 B9876;2022-01-07;8 B9876;2022-02-03;5 B9876;2022-02-09;4 B9876;2022-10-18;7 . |
Sliding windows and the frame clause
Points are consumed by expiry under the usual rules: rewards granted earlier are removed once they fall out of the retention window. A windowing function with a frame clause expresses exactly that:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT *, array_agg(points) OVER (ORDER BY d RANGE BETWEEN '6 months' PRECEDING AND CURRENT ROW) FROM t_bonus_card WHERE card_number = 'A4711' ; card_number | d | points | array_agg -------------+------------+--------+------------- A4711 | 2022-01-01 | 8 | {8} A4711 | 2022-01-04 | 7 | {8,7} A4711 | 2022-02-12 | 3 | {8,7,3} A4711 | 2022-05-05 | 2 | {8,7,3,2} A4711 | 2022-06-07 | 9 | {8,7,3,2,9} A4711 | 2023-02-02 | 4 | {4} A4711 | 2023-03-03 | 7 | {4,7} A4711 | 2023-05-02 | 1 | {4,7,1} (8 rows) |
Rows are processed in date order, and for each row the frame covers the range between six months before the current row and the current row. Aggregating into an array makes the members of the frame visible: on June 7th the array holds five entries. array_agg is a useful debugging aid, but production queries need the actual total, so it is replaced by sum:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT *, sum(points) OVER (ORDER BY d RANGE BETWEEN '6 months' PRECEDING AND CURRENT ROW) FROM t_bonus_card WHERE card_number = 'A4711' ; card_number | d | points | sum -------------+------------+--------+----- A4711 | 2022-01-01 | 8 | 8 A4711 | 2022-01-04 | 7 | 15 A4711 | 2022-02-12 | 3 | 18 A4711 | 2022-05-05 | 2 | 20 A4711 | 2022-06-07 | 9 | 29 A4711 | 2023-02-02 | 4 | 4 A4711 | 2023-03-03 | 7 | 11 A4711 | 2023-05-02 | 1 | 12 (8 rows) |
Points drop again in 2023, matching the intended expiry behaviour.
Why RANGE instead of ROWS
Frame clauses accept ROWS, RANGE and GROUP. ROWS counts physical rows backwards, which is meaningless when rewards arrive at irregular times; the requirement is a time interval, and RANGE is the keyword that provides it.
Separating cards and resetting periods with PARTITION BY
The queries above compute values for a single card. To generalise, partition by the card so each card’s history is processed independently and one participant’s points never mix with another’s:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SELECT *, sum(points) OVER (PARTITION BY card_number, date_trunc('year', d) ORDER BY d RANGE BETWEEN '6 months' PRECEDING AND CURRENT ROW) FROM t_bonus_card ; card_number | d | points | sum -------------+------------+--------+----- A4711 | 2022-01-01 | 8 | 8 A4711 | 2022-01-04 | 7 | 15 A4711 | 2022-02-12 | 3 | 18 A4711 | 2022-05-05 | 2 | 20 A4711 | 2022-06-07 | 9 | 29 A4711 | 2023-02-02 | 4 | 4 A4711 | 2023-03-03 | 7 | 11 A4711 | 2023-05-02 | 1 | 12 B9876 | 2022-01-07 | 8 | 8 B9876 | 2022-02-03 | 5 | 13 B9876 | 2022-02-09 | 4 | 17 B9876 | 2022-10-18 | 7 | 7 (12 rows) |
The same mechanism handles a yearly reset. Rounding the timestamps out to full years turns the year into a partition criterion, so counting starts from zero at the beginning of every year while expiry continues to apply within it.
Takeaway
The calculations span a handful of SQL statements and cover point balances at any point in time, expiration after a set number of months, and annual resets. None of that requires client-side code — the work is pushed into the database where the data already lives.
