LISTING 5: Query to Create Continuous Subscription Table

SELECT a.subscriber_name, a.magazine_name, a.subscription_start,
MIN(b.subscription_end) AS subscription_end
FROM (SELECT DISTINCT subscriber_name, magazine_name, subscription_start
FROM magazine_subs m
  WHERE NOT EXISTS (SELECT * FROM magazine_subs m2
  WHERE m2.subscriber_name = m.subscriber_name
  AND m2.magazine_name = m.magazine_name
  AND m2.subscription_start < m.subscription_start
  AND m2.subscription_end >= m.subscription_start)
  ) a JOIN (
    SELECT DISTINCT subscriber_name, magazine_name, subscription_end
    FROM magazine_subs m
    WHERE NOT EXISTS (SELECT * FROM magazine_subs m2
    WHERE m2.subscriber_name = m.subscriber_name
    AND m2.magazine_name = m.magazine_name
    AND m2.subscription_end > m.subscription_end
    AND m2.subscription_start <= m.subscription_end)
    ) b ON
  b.subscriber_name = a.subscriber_name
  AND b.magazine_name = a.magazine_name
  AND b.subscription_end >= a.subscription_start
GROUP BY a.subscriber_name, a.magazine_name, a.subscription_start