Write an SQL query that, given a table with columns id, year, and revenue, returns for each row the previous year's revenue and the revenue difference.

You can solve this with a window function that looks back one row per id. Assuming the table is called revenue_data:

SELECT
  id,
  year,
  revenue,
  LAG(revenue) OVER (PARTITION BY id ORDER BY year) AS previous_year_revenue,
  revenue - LAG(revenue) OVER (PARTITION BY id ORDER BY year) AS revenue_difference
FROM revenue_data
ORDER BY id, year;

Explanation:

• PARTITION BY id ensures the lag is calculated separately for each entity.
• ORDER BY year guarantees the rows are in chronological order.
• LAG(revenue) returns the revenue from the previous year; if none exists, it returns NULL.
• The difference is simply the subtraction of the two values.

Complexity: The query scans the table once and performs a constant‑time window operation per row, so O(n) time and O(1) additional space (aside from the result set).