SQL interview question: Compare to the Previous Row With LAG
Compare Sales to Previous Day
Trading analytics wants to show daily stock price changes by comparing each day to the previous day.
Show the current closing_price and the previous day's price side-by-side, then calculate the change.
Return price_date, current_price, previous_price, and price_change. Exclude the first day. Order by date.
Skills: LAG() window function for accessing previous row
Tables
stock_prices 10 rows
- price_date text PK
- closing_price real
| price_date | closing_price |
|---|---|
| 2025-01-01 | 150.0 |
| 2025-01-02 | 152.5 |
| 2025-01-03 | 148.75 |