Is there any order of precedence in window functions of SQL?

Question

I have a table as follows:
I just want the previous entry for every row from the column 'marks'.
Table structure:

 test_id    marks 
   100        55
   101        55
   102        60
   103        58
   104        40

This is my query:

select *,LAG(marks,1,marks) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as prev

from student_tests

This is the output:

      test_id    marks 
        100        55
        101        55
        102        60
        103        58
        104        40

The output is as per my expectation. But, I am unable to figure out if it makes sense when I give 1 as the offset in the part LAG(marks,1,marks) along with the window.

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Because, I am in the sense asking SQL to look 'UNBOUNDED' PRECEDING and then giving 1 in the offset doesn't seem sensible to me. If my understanding is correct, query must have given me the error. But, it didn't. So, I am wondering whether there's any order of precedence on the play under the hood.

Am I missing something?
Please help me understand this.

Edit: Respecting the Stackoverflow policy, I have removed the images and tried alternatives.

Answer 1

The lag and lead functions are special, in that they ignore the frame clause. So it makes no difference if you specify ROWS or RANGE or what the window is. It always just looks forwards or back the specified number of rows. You can prove this by looking at results from the lead function. In most other DBMSs it's not even valid to specify a window frame for lead and lag.

The default window specification is in any case RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so it's not necessary to specify it even in functions where it does make sense. Moreover, RANGE is almost never what you want, you nearly always want ROWS.

Furthermore, lag and lead only make sense with a unique ORDER BY specification, otherwise "Without ORDER BY, rows are processed in an unspecified order". As always in SQL, if you do not specify ORDER BY then all bets are off.

The default number of rows is 1, so it's not necessary to specify it unless you also want to specify the default value.

So a more normal-looking lag call would be

LAG(marks, 1, marks) OVER (ORDER BY test_id) as prev