Interview Question Bank · LAG · PARTITION BY · filtering after a window
6. Price change history
MediumE-commerceQuestion #90
Concept: LAG · PARTITION BY · filtering after a windowshow
LAG(col) OVER (PARTITION BY key ORDER BY time) puts the previous row's value next to the current one, so each row can be compared with the one before it. Without PARTITION BY, the first row of one key is compared with the last row of another. The first row of each key has no previous value (NULL), and since window functions run after WHERE, filter on the LAG result in an outer query.
Example (different data):
WITH r AS (
SELECT plan, effective_on, fee,
LAG(fee) OVER (PARTITION BY plan ORDER BY effective_on) AS prev_fee
FROM plan_fees
)
SELECT * FROM r WHERE fee > prev_fee;Your task
Quillmere's pricing team wants an audit trail of every real list-price change. The price feed sometimes re-publishes a price that didn't change.
From shop_price_history, return product (product name), changed_on, old_price (the product's previous price record), new_price (this record's price) and change_pct (100 * (new_price - old_price) / old_price, rounded to 2 decimals). Only include records where the price actually changed from the previous record; a product's first record is not a change. Sort by product, then changed_on.
Starting Postgres in your browser…
Checking your access…