Interview Question Bank · Self-join on month + 1 · DISTINCT before joining · year boundaries
17. Same product bought two months running
HardE-commerceQuestion #284
Concept: Self-join on month + 1 · DISTINCT before joining · year boundariesshow
To find something that happens in two consecutive months, reduce the data to one row per (who, what, month) with DISTINCT, then self-join where the second month equals the first plus INTERVAL '1 month'. Comparing month numbers (EXTRACT(MONTH) + 1) breaks from December to January. Skipping the DISTINCT step multiplies pairs whenever someone bought the same thing twice in one month.
Example (different data):
WITH m AS (
SELECT DISTINCT patient_id, drug, date_trunc('month', filled_on)::date AS mon
FROM prescription_fills
)
SELECT a.patient_id, a.drug, a.mon AS first_month
FROM m a
JOIN m b ON b.patient_id = a.patient_id AND b.drug = a.drug
AND b.mon = a.mon + INTERVAL '1 month';Your task
Brindlewood Market wants to offer subscribe-and-save to customers who already rebuy the same product month after month. As a first cut, they want every case of a customer buying the same product in two back-to-back calendar months, counting completed orders only.
Return one row per customer, product and pair of consecutive months in which that customer bought that product: name, product (product name), first_month and second_month (both YYYY-MM). Sort by name, product, then first_month.
Starting Postgres in your browser…
Checking your access…