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 |
Practice on your iPhone
Query Dojo for iPhone has 85 interview questions, timed mock interviews and a tile builder, so you can write queries without a keyboard.