Advanced SQL

Gaps, Islands, and Relational Division

Gaps-and-islands finds consecutive runs by comparing each value with its predecessor, while relational division answers universal “has every required item” questions with nested nonexistence or exact counts.

An island begins where no qualifying predecessor connects.
Consecutiveness needs an exact domain rule.
Relational division expresses universal quantification.
The empty divisor is a deliberate edge case.
Duplicates must not inflate count-based division.
Null requirement keys make equality ambiguous.

Exact consecutive paid-order date islands

WITH days AS (
  SELECT DISTINCT customer_id, CAST(placed_at AS DATE) AS order_day
  FROM orders WHERE status = 'PAID'
), marked AS (
  SELECT days.*, CASE
    WHEN LAG(order_day) OVER (PARTITION BY customer_id ORDER BY order_day)
         = order_day - INTERVAL '1 day' THEN 0 ELSE 1 END AS island_start
  FROM days
), numbered AS (
  SELECT marked.*, SUM(island_start) OVER (
    PARTITION BY customer_id ORDER BY order_day ROWS UNBOUNDED PRECEDING
  ) AS island_id
  FROM marked
)
SELECT customer_id, MIN(order_day) AS start_day, MAX(order_day) AS end_day, COUNT(*) AS day_count
FROM numbered GROUP BY customer_id, island_id
ORDER BY customer_id, start_day;

Customers who bought every product in a required set

SELECT c.customer_id
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1 FROM required_products AS r
  WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    JOIN order_items AS i ON i.order_id = o.order_id
    WHERE o.customer_id = c.customer_id
      AND o.status = 'PAID'
      AND i.product_id = r.product_id
  )
);

Worked division: requirements {A, B}
Customer purchases	Missing requirement exists?	Qualifies?
7: A, A, B	No	Yes; duplicate A is irrelevant
8: A, C	Yes: B	No
9: none	Yes: A and B	No
Any customer; requirements empty	No	Yes under vacuous truth