Drill 13 of 17 in E-commerce questions
0

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…