Write a query to find all products whose price increased in the last price update compared to the previous one.

Answer

Compare each price update to the previous one for the same product using LAG(), then keep rows where the price increased.

Solution

SELECT product_id, update_date, price, prev_price
FROM (
  SELECT product_id, update_date, price,
         LAG(price) OVER (PARTITION BY product_id ORDER BY update_date) AS prev_price
  FROM PriceHistory
) x
WHERE price > prev_price;

Step by Step

1. PARTITION BY product_id ensures price comparisons only happen within the same product's own history, never across different products.
2. LAG(price) OVER (... ORDER BY update_date) pulls in the immediately preceding price update for that product.
3. The outer WHERE price > prev_price keeps only rows where the price went up compared to the prior update.

Alternative Method: Self-Join on Row Position

SELECT curr.product_id, curr.update_date, curr.price, prev.price AS prev_price
FROM PriceHistory curr
JOIN PriceHistory prev
  ON curr.product_id = prev.product_id
  AND prev.update_date = (
    SELECT MAX(update_date) FROM PriceHistory p2
    WHERE p2.product_id = curr.product_id AND p2.update_date < curr.update_date
  )
WHERE curr.price > prev.price;

This self-join achieves the same comparison without a window function, useful on engines that don't support LAG() - though it's noticeably more verbose and re-runs a correlated subquery per row.

Key Exam Traps

• The very first price entry for any product has no previous row - LAG() correctly returns NULL for it, and the comparison price > NULL is never true, so it's automatically (and correctly) excluded.
• Forgetting PARTITION BY would compare a product's price to whatever the immediately-preceding row is in the entire table, potentially across a different product entirely - a serious logic bug, not just a stylistic issue.
• Same-day multiple updates: If a product's price changes more than once on the same update_date, ordering by date alone doesn't guarantee a deterministic "previous" price - a secondary tiebreaker column (like a timestamp or sequence ID) is needed for a fully reliable result.