Introduction

A window function that allows us to retrieve the value of a column from a previous row in the result set.

Sometimes we may want to calculate values on a rowing basis. In my experience those times we'll want to compare the previous days values with the current days values.

In order to do that SQL provides the LAG keyword.

Example 1:

How to carry a field from a previous row to its subsequent row?

Starting:

SELECT
    ticker,
    trade_date AS current_date
FROM
    cerberus.chart_premiums_paid_aggregate
WHERE
    ticker = 'NKE'
LIMIT 3

Results:

So I have rows that have tickers and dates. To access a previous rows values I'll add a LAG to the SELECT part of my query.

SELECT
    ticker,
    trade_date AS current_date,
    LAG(trade_date) OVER (
        ORDER BY trade_date
    ) AS prev_date
FROM
    cerberus.chart_premiums_paid_aggregate
WHERE
    ticker = 'NKE'
LIMIT 3

Example 2:

How could we use this technique in calculating open fractional options?

Conclusion

This technique is powerful used for values which involve heavy processing.