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…