Handling Bonus Programs in SQL

Bonus cards, “Miles & more”, bonus points - don’t we all love and hate them at the same time? Recently we had an interesting use case which made me think about sharing some of the techniques we used in this area to reduce client code by writing some clever SQL. This post will show you how to efficiently code bonus programs in SQL.

Getting started coding bonus programs in SQL

Suppose we want to run a bonus program. What we want is to know how many bonus points somebody had at any given point in time. This is how we might want to store the data:

For each bonus card, we want to store how many points were awarded when. So far this is relatively easy. Let's load some sample data:

In my example, we have data for two bonus cards which receive some rewards from time to time. To run our bonus program using PostgreSQL, we might want to answer some basic questions:

• How many bonus points does a participant have at any point in time?
• What is the number of points the participant has in case bonus points expire after a certain number of months?
• How does the number of bonus points look if we start to count all over at the beginning of every year, and in case bonus points expire after some time?

Let's answer these questions using…

Windowing functions and advanced frame clauses for bonus programs in SQL

To answer all these questions we can use windowing functions along with some advanced, fancy frame clauses. Let's take a look at a basic query:

What this does is simple: It goes through our data set line by line (sorted by date). Then it checks if there are rows between our current row and a value 6 months earlier. For debugging purposes, we aggregate those values into an array. What we see is that on June 7th we have 5 entries. But keep in mind: The rules of our bonus program say that points awarded are taken away after 6 months. By using a sliding window, we can easily achieve this goal.

Using RANGE

Note that in SQL we have “ROWS”, “RANGE” and “GROUP” as possible keywords in our frame clause. ROWS 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 RANGE can do for us. Rewards might be granted at random points in time so we certainly need to operate with intervals here.

The array_agg function is really useful to debug things. However, in a real world scenario, we need to add up those numbers using sum:

We have seen that points drop in 2023 again. That's exactly what we wanted.

Windowing for bonus programs in SQL: PARTITION BY

Maybe you have noticed that we did the entire calculation for just one card number. However, what has to be done to make this work for any number of cards? The answer is PARTITION BY:

PARTITION BY card_number ensures 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 that at the beginning of every year those points should be set to zero and counting should 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.

Finally …

If you want to know more about PostgreSQL 15 and if you are interested in merging data, check out my post about MERGE - which can be found here.