Drill 17 of 17 in E-commerce questions
0

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…