UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING Examples

I’m familiar with using the OVER clause and don’t think it’s too uncommon to see it used for including row numbers by using ROW_NUMBER() and aggregating data. But even though they’ve been around since SQL Server 2012, I’m not too familiar with using the OVER clause with the UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING to affect the window being queried.

Let’s take a look at a couple of examples using UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING.

UNBOUNDED PRECEDING Example

We’ll start out with data on comics purchased along with the release and try to gauge the popularity of each. Here’s our initial data:

We can use UNBOUNDED PRECEDING to add an additional column that shows the minimum purchased value over time. To get those results, we can run this query:

SELECT ReleaseDate,
       ComicId,
       Purchased,
       MIN(Purchased) OVER (
           PARTITION BY ComicId 
           ORDER BY ReleaseDate 
           ROWS UNBOUNDED PRECEDING
       ) AS MinPurchasedDate
FROM Comic
ORDER BY ComicId,
         ReleaseDate;

Which gives us:

Let’s take a closer look at each comic, starting with ComicId 101.

Looking at ComicId 101, we see the MinPurchased stayed consistent. It started at 45 and remained at 45 over the timeframe.

ComicId 102 is different.

ComicId 102 starts at 50 purchases and then drops almost every week. The only week it did not drop is 2024-12-17, where purchases went from 40 up to 45. We see the MinPurchasedDate stayed steady at 40 on 2024-12-17 and then dropped to 35 on 2024-12-28. This tells us that based on purchases, the popularity of ComicId 102 is falling.

For the final comic of the example, let’s look at ComicId 103.

This comic stays consistent, hovering between 45 and 55, with a MinPurchasedDate hovering around 45-50.

So what did MinPurchasedDate tell us about falling popularity? ComicId 1 is not trending down, ComicId 2 is trending down, and ComicId 3 had a slight downtrend.

UNBOUNDED FOLLOWING Example

We can use UNBOUNDED FOLLOWING to reverse what we want MinPurchased to be based on:

SELECT ReleaseDate,
       ComicId,
       Purchased,
       MIN(Purchased) OVER (
           PARTITION BY ComicId 
           ORDER BY ReleaseDate 
           ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
       ) AS MinPurchased
FROM Comic
ORDER BY ComicId,
         ReleaseDate;

Instead of the MinPurchased being tracked in chronological order, it’s now in reverse chronological order.

I won’t look at all values here, but focusing on ComicId 101 again, we see the MinPurchased column updates as we go back in time from 2024-12-30 to 2024-11-05.

Boundless Opportunities

UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING can provide one more tool to add to your toolkit when it comes to analyzing data trends over time. I hope this post provides some inspiration for seeing if they fit with your workflow.

Thanks for reading!