SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

Interview Drills · LEAD() · PARTITION BY

37. Days until next order

Concept: LEAD() · PARTITION BYshow

LEAD(col) reads col from the next row of the window, LAG(col) from the previous one. With PARTITION BY customer_id ORDER BY order_date, LEAD(order_date) is that customer's next order date, and the customer's last row gets NULL because there is no next row.

Example (different data):

SELECT patient_id, visit_date,
  LEAD(visit_date) OVER (PARTITION BY patient_id ORDER BY visit_date) AS next_visit
FROM visits;

Your task

Retention analysts want the repurchase gap: how many days pass between a customer's completed order and their next one.

For every completed order, return customer_id, order_id, order_date and days_to_next (whole days until the same customer's next completed order; NULL for their last one). Sort by customer_id, order_date.

Starting Postgres in your browser…

Checking your access…