Interview Question Bank · ROW_NUMBER · self-join on rn = 1 and rn = 2 · date difference
10. Second order within 30 days
HardE-commerceQuestion #94
Concept: ROW_NUMBER · self-join on rn = 1 and rn = 2 · date differenceshow
Questions about a customer's 1st and 2nd purchase need the orders numbered per customer: ROW_NUMBER() OVER (PARTITION BY customer ORDER BY date, id). Join the numbered set to itself (rn = 1 with rn = 2) to put both on one row. LAG would compare every pair of neighbouring orders, not just the first two. In Postgres, date - date is a whole number of days.
Example (different data):
WITH n AS (
SELECT patient_id, visit_on,
ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY visit_on) AS rn
FROM clinic_visits
)
SELECT a.patient_id, b.visit_on - a.visit_on AS days_to_follow_up
FROM n a
JOIN n b ON b.patient_id = a.patient_id AND b.rn = 2
WHERE a.rn = 1;Your task
Quillmere's lifecycle team wants to know which customers came back for a second purchase within 30 days of their first, the window their welcome email targets.
Number each customer's completed orders by ordered_on (then id). Return name, first_order_on, second_order_on and days_between (second minus first, in days) for every customer whose second completed order came at most 30 days after their first (exactly 30 counts). Sort by name.
Starting Postgres in your browser…
Checking your access…