Interview Question Bank · LEAD · PARTITION BY · filter in an outer query
13. Next order bigger than this one
EasyE-commerceQuestion #275
Concept: LEAD · PARTITION BY · filter in an outer queryshow
LEAD(col) OVER (PARTITION BY customer ORDER BY date) puts the next row's value beside the current one, within the same customer. Window functions run after WHERE, so you can't filter on LEAD in the same query: compute it in a CTE, then filter outside. Remove rows that shouldn't count (like cancelled orders) before the window, or LEAD will treat them as the next order.
Example (different data):
WITH v AS (
SELECT patient_id, visit_date, weight_kg,
LEAD(weight_kg) OVER (PARTITION BY patient_id ORDER BY visit_date) AS next_weight
FROM clinic_visits
)
SELECT * FROM v WHERE next_weight < weight_kg;Your task
Brindlewood Market, an online home-goods store, wants to know how often a customer's next purchase is bigger than the one before it. For this question, cancelled orders never happened.
For every completed order where the same customer's next completed order (by order_date) has a strictly larger amount, return name, order_id, order_date, amount, next_amount and increase (next_amount - amount). Sort by order_date, then order_id.
Starting Postgres in your browser…
Checking your access…