Find the largest sum of three sequential values in SQL?

Question

Say I have the following table, called revenues.

id | revenue
------------
1  | 345
2  | 5673
3  | 0
4  | 45
5  | 4134
6  | 35
7  | 533
8  | 856
9  | 636
10 | 35

I want to find the largest sum of the grouping of sequential 3 values. Here's what I mean:

ids  1 + 2 + 3   =>  345  + 5673 + 0    = 6018
ids  2 + 3 + 4   =>  5673 + 0    + 45   = 5718
ids  3 + 4 + 5   =>  0    + 45   + 4134 = 4179
ids  4 + 5 + 6   =>  45   + 4134 + 35   = 4214
ids  5 + 6 + 7   =>  4134 + 35   + 533  = 4702
ids  6 + 7 + 8   =>  35   + 533  + 856  = 1424
ids  7 + 8 + 9   =>  533  + 856  + 636  = 2025
ids  8 + 9 + 10  =>  856  + 636  + 35   = 1527

In this case, I would want the result to be 6018, since it's the largest sum of 3 sequential values. I'm just starting to learn SQL, with my only other previous language being Java, and all I can think is how easy this would be to do with a for loop. Does anyone have any idea on how I could get started writing a query like this? Does a similar thing exist in SQL?

Edit: Furthermore, is it possible to scale something like this? What if I had a really big table and I wanted to find the largest sum of a hundred sequential values?

Answer 1

One approach would be to use two joins to get to id+1 and id+2:

SELECT max(t1.revenue+t2.revenue+t3.revenue)
FROM revenues t1
JOIN revenues t2 ON t1.id+1 = t2.id
JOIN revenues t3 ON t1.id+2 = t3.id

Demo.

Answer 2

If your database supports the lag() window function, you can retrieve the result in a single table scan:

select  max(rev3)
from    (
        select  revenue + 
                    lag(revenue) over (order by id) + 
                    lag(revenue, 2) over (order by id) as rev3
        from    revenues
        ) as SubQueryAlias

See it working at SQL Fiddle.

Answer 3

with t as (
SELECT 1 as id, 345 as rev
UNION SELECT 2, 5673
UNION SELECT 3, 0
UNION SELECT 4, 45
UNION SELECT 5, 4134
UNION SELECT 6, 35
UNION SELECT 7, 533
UNION SELECT 8, 856
UNION SELECT 9, 636
UNION SELECT 10, 35)
SELECT TOP 1 id, SUM (rev) OVER (ORDER BY id ROWS 2 PRECEDING) r
FROM t
ORDER BY r desc;

Provides answer 3, 6018* on SQL Server 2012.

EDIT Query that makes sure that we only get rows that are made up from 3 revenues:

with t as (
SELECT 1 as id, 345 as rev
UNION SELECT 2, 5673
UNION SELECT 3, 0
UNION SELECT 4, 45
UNION SELECT 5, 4134
UNION SELECT 6, 35
UNION SELECT 7, 533
UNION SELECT 8, 856
UNION SELECT 9, 636
UNION SELECT 10, 35)
SELECT TOP 1 id, r FROM 
  (SELECT  id
    , SUM (rev) OVER (ORDER BY id ROWS 2 PRECEDING) r
    , SUM (1) OVER (ORDER BY id ROWS 2 PRECEDING) cnt
  FROM t) as subslt
WHERE cnt = 3
ORDER BY r desc;

---

*Actually non-deterministic between 3, 6018 and 2, 6018. The second/edited query is deterministic.