Second greatests running value with SQL

The idea is to create an sorted cummulative array and take second element.

PostgreSQL(it could be easily extended if 3rd/4th/5th element is required by adjusting array index):

SELECT t.*,
     (sort(ARRAY_AGG(t.value) OVER(ORDER BY t.n), 'desc'))[2] AS sec_max
FROM t
ORDER BY n;

db<>fiddle demo

Ufnortunately Snowflake does not support cummulative ARRAY_AGG/STRING_AGG.

Below the version that build cummulative array using recursive cte, then array is sorted and second element is taken.

Data prep:

CREATE OR REPLACE TABLE t
AS
SELECT 1 AS n, 1 AS value
UNION ALL SELECT 2,4
UNION ALL SELECT 3,4
UNION ALL SELECT 4,6
UNION ALL SELECT 5,4
UNION ALL SELECT 6,8;

Helper function:

CREATE OR REPLACE FUNCTION array_sort_desc(a array)
  RETURNS array
  LANGUAGE JAVASCRIPT
AS
$$
  return A.sort().reverse();
$$
;

Main query:

WITH src AS (
   SELECT *, ROW_NUMBER() OVER(ORDER BY n) AS rn FROM t
),cte AS (
  SELECT *,  ARRAY_CONSTRUCT(src.value) AS arr
  FROM src
  WHERE rn=1
  UNION ALL
  SELECT src.*, ARRAY_APPEND(arr, src.value)
  FROM src
  JOIN cte
    ON cte.rn=src.rn-1
)
SELECT cte.n, cte.value, arr, array_sort_desc(arr), array_sort_desc(arr)[1] AS sec_max
FROM cte
ORDER BY n;
/*
+---+-------+-----------------------------------+-----------------------------------+---------+
| N | VALUE |                ARR                |       ARRAY_SORT_DESC(ARR)        | sec_max |
+---+-------+-----------------------------------+-----------------------------------+---------+
| 1 |     1 | [   1  ]                          | [   1  ]                          |         |
| 2 |     4 | [   1,   4  ]                     | [   4,   1  ]                     |       1 |
| 3 |     4 | [   1,   4,   4  ]                | [   4,   4,   1  ]                |       4 |
| 4 |     6 | [   1,   4,   4,   6  ]           | [   6,   4,   4,   1  ]           |       4 |
| 5 |     4 | [   1,   4,   4,   6,   4  ]      | [   6,   4,   4,   4,   1  ]      |       4 |
| 6 |     8 | [   1,   4,   4,   6,   4,   8  ] | [   8,   6,   4,   4,   4,   1  ] |       6 |
+---+-------+-----------------------------------+-----------------------------------+---------+
*/

I'm providing a separate answer because this one is so different (and I may delete the other answer). I am convinced this can be handled only with window functions. And I think this offers a solution.

This starts with a bunch of explanation. You can skip down for the query and link to db<>fiddle.

There are two cases where the second max is really simple:

• If the current value is the maximum value and it has appeared before, then it is the second max.
• If the current value is the maximum value and it has never appeared, then the previous maximum is the second max.

An additional simple case:

• If the value is less or equal to the previous second max, the second max does not change.

And finally, an important property of the second max:

• The second max is increasing.

So, the idea is to do the following:

1. Calculate the "easy" cases.
2. Assign the second in the "trivial" cases, when it doesn't change based on the easy cases.
3. Assign the current value in the rest.
4. Do a cumulative max of the second sum.

This results in:

select t.*, max(imputed_second_max) over (order by n) as second_max
from (select t.*,
             (case when sometimes_mx_2 is not null then sometimes_mx_2
                   when value <= max(sometimes_mx_2) over (order by n) then max(sometimes_mx_2) over (order by n)
                   else value
              end) as imputed_second_max
      from (select t.*,
                   (case when value = mx and nth_value > 1 then value
                         when value = mx and nth_value = 1 then lag(mx) over (order by n)
                    end) as sometimes_mx_2
            from (select t.*, max(value) over (order by n) as mx,
                         row_number() over (partition by value order by n) as nth_value
                  from t
                 ) t
           ) t
      ) t
order by n;

I found that I needed to augment the test cases to get better coverage. I found that decreasing sequences are particularly tricky.

Here is a db<>fiddle.

You can use a lateral join:

select t.*, t2.value as second_mx
from t cross join lateral
     (select t2.*
      from t t2
      where t2.n <= t.n
      order by t2.value desc
      offset 1 row fetch first 1 row only
     ) t2;

Actually, you can also express this as a correlated subquery.

That said, this will not have good performance on a non-smallish table.