Drill 8 of 12 in Food delivery questions
0

Interview Question Bank · LAG per pair · interval comparison

8. Reorders from the same restaurant within 7 days

MediumFood deliveryQuestion #236

Concept: LAG per pair · interval comparisonshow

LAG(x) OVER (PARTITION BY a, b ORDER BY t) fetches the previous row's value within the same (a, b) group, which is how you compare each event with the one before it. Subtracting timestamps gives an interval, and comparing it with INTERVAL '7 days' keeps the hours. Casting to dates first throws the time of day away, so 7 days and 2 hours would pass as 7.

Example (different data):

SELECT patient_id, clinic, visited_at,
  visited_at - LAG(visited_at) OVER (PARTITION BY patient_id, clinic ORDER BY visited_at) AS gap
FROM visits;

Your task

Brightbowl's loyalty team is looking for habit-forming moments: a customer ordering again from the same restaurant within a week of their last delivered order there.

Using delivered orders only, find every order placed at most 7 days (7 × 24 hours, inclusive) after the same customer's previous delivered order from the same restaurant. Return customer (name), restaurant (name), previous_order_at (placed_at of that previous order) and reorder_at (placed_at of the reorder). Sort by reorder_at, then customer.

Starting Postgres in your browser…

Checking your access…