Well... The query didn't work! Instead of the latest opening date, we saw the current opening date.
In order to understand why this happened, we need to refer to the previous part of our course, where we talked about default window frames:
If there is an
ORDER BY clause,
RANGE UNBOUNDED PRECEDING will be used as the default window frame.
And this is precisely the cause of our troubles. We indeed used
ORDER BY within
OVER(...), which is why
LAST_VALUE(x) only considers the rows from the first row until the current row. The solution is quite simple: we need to define the right window frame:
ORDER BY opened
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING)