MANAGING SQL ADD-ON PROGRAMS 

Preamble

We all love and hate bonus cards, “Miles & more,” and bonus points at the same time, don’t we? Recently, we encountered an intriguing use case that got me to thinking about how we could reduce client code by writing some clever SQL. You can learn how to efficiently code bonus programs in SQL from this post.

Getting started coding bonus programs in SQL

Say that we want to manage a bonus scheme. We are interested in knowing how many bonus points a person had at any given time. We may want to store the data in the following manner:

CREATE TABLE t_bonus_card ( card_number text NOT NULL, d date, points int );

If we want to keep track of when and how many points were given out for each bonus card. So far, this is relatively easy. Let’s load some sample data:

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 \.

In my example, we have information for two bonus cards that occasionally receive rewards. We may want to respond to a few fundamental inquiries in order to run our bonus program using PostgreSQL:

• How many bonus points does each player currently have?
• What number of points does the participant have if bonus points expire after a specific period of time?
• What would the total number of bonus points look like if we had to start over at the beginning of each year and assumed that bonus points would eventually expire?

Let’s answer these questions using…

Windowing functions and advanced frame clauses for bonus programs in SQL

We can use windowing functions and some sophisticated, fancy frame clauses to address all of these questions. Let’s look at a straightforward example.

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)

The result is straightforward: It goes line by line through our data set, which is sorted by date. The next step is to see if there are any rows between the current row and a value from six months ago. We combine those values into an array for debugging purposes. We can see that there were 5 entries on June 7. However, keep in mind that, according to the terms of our bonus program, earned points expire after six months. Using a sliding window, we can quickly accomplish this.

With RANGE

Note that in SQL, we have “ROWS”, “,RANGE” and “,GROUP” as possible keywords in our frame clause, whichROWS means that we want to see a specific number of older rows in our frame. However, this makes no sense here – what we need is an interval, and this is exactly what it can provide for us. Rewards could be given out at random times, so we need to work with intervals here.

The array_agg. However, in a real-world scenario, we need to add up those numbers using sum:

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)

That points decline in 2023 has already happened. That is precisely what we desired.

Windowing for bonus programs in SQL: PARTITION BY

We performed the entire calculation for just one card number, as you may have noticed. What needs to be done, though, in order for this to work with any quantity of cards? The answer is PARTITION BY:

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)

PARTITION BY card_numberensures that our calculations are done for each incarnation of card_number separately. In other words, User A’s points cannot be mixed with User B’s points anymore. But there is more to this query: We want those points to be reset to zero at the start of each year, and counting to resume. We can achieve this by using PARTITION BY as well. By rounding out dates to full years, we can use the year as partition criteria.

As you can see, SQL is really powerful. A lot can be done without having to write a single line of client code. A handful of SQL statements can produce terrific results, and it makes sense to leverage your application.

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​