26. First and Last Close per Symbol
HardWindow FunctionsFIRST_VALUELAST_VALUEexplicit frame gotchaWINDOW clause
LAST_VALUE has a famous trap: with an ORDER BY the default
frame ends at the current row, so LAST_VALUE returns the current row, not
the last one. You must widen the frame to the whole partition.
Task: for each price row return symbol, day, close, plus
first_close and last_close — the earliest and latest close for that symbol
(by day). Order by symbol, then day.
Tables
Loading schema…
Expected outputrows must come back in this order
Computing expected output…
This is the result for the example data above. On Submit your query is graded against this example plus 4 hidden edge cases — 5test cases in all. A sloppy query that only fits the example won't pass.
SQL· SQLite · runs in your browser
⌘/Ctrl+↵ run · ⇧⌘/Ctrl+↵ submit
Run a query to see its output here.