Compare Row Values Using LEAD() and LAG() Window Functions

-- Scenario: Track daily stock price changes for a specific stock
-- Assuming a 'stock_prices' table with stock_symbol, trade_date, and closing_price

SELECT
    stock_symbol,
    trade_date,
    closing_price,
    LAG(closing_price, 1) OVER (PARTITION BY stock_symbol ORDER BY trade_date) AS previous_day_price,
    LEAD(closing_price, 1) OVER (PARTITION BY stock_symbol ORDER BY trade_date) AS next_day_price,
    closing_price - LAG(closing_price, 1) OVER (PARTITION BY stock_symbol ORDER BY trade_date) AS price_change
FROM
    stock_prices
WHERE
    stock_symbol = 'GOOG'
ORDER BY
    trade_date;

How it works: This snippet utilizes the `LEAD()` and `LAG()` window functions to compare values between sequential rows. `LAG(column, offset)` retrieves the value of `column` from a row `offset` rows *before* the current row within its partition, ordered as specified. `LEAD(column, offset)` does the same for a row `offset` rows *after* the current row. Here, it's used to fetch the previous and next day's closing prices for a stock, enabling the calculation of daily price changes. `PARTITION BY stock_symbol` ensures comparisons are made only within the same stock's data. These functions are invaluable for time-series analysis and trend detection.