You use your DB's lead() or lag() functions with some subquery nesting. Examples lifted from a recent nearly identical question:
lead()
lag()
And more here.