Interval overlap pattern

What is interval overlap?

Interval overlap is when a record spans a date range, and you want to count it in every period that range touches.

For example, an order is open from the day it is placed until the day it is delivered. Operations wants to know how many orders were open each month, and what they were worth. No column links an order to a month. The pairing is computed from dates.

The same shape appears in many places:

• Open support tickets per week
• Active subscriptions or MRR per month
• Active headcount per month from employment start and end dates

Many-to-many relationship between orders and dim_dates

• [HTML node] — orders: highlighted columns take part in the relationship
• [HTML node] — dim_dates: highlighted columns take part in the relationship

• orders → dim-dates (many_to_many order_date <= date < deliver_date keys: orders.id, dim_dates.date)

Sample data:

orders

id	order_date	deliver_date	amount
o1	2024-01-10	2024-01-20	100
o2	2024-01-25	2024-02-05	200
o3	2024-02-10	NULL	50
o4	2024-03-01	2024-03-01	80

dim_dates (one row per day, 91 rows)

date	month
2024-01-01	Jan
2024-01-02	Jan
...	...
2024-01-31	Jan
2024-02-01	Feb
...	...
2024-02-29	Feb
2024-03-01	Mar
...	...
2024-03-31	Mar

An order is open on a day if it was placed on or before that day and not yet delivered. The delivery day itself does not count as open. So o3 is still open, and o4, delivered the day it was placed, is never open.

Which months each order is open in

• Date
• dim_dates
• Orders
• 1 Jan
• 1 Feb
• 1 Mar
• 1 Apr
• January — 31 daily rows
• February — 29 daily rows
• March — 31 daily rows
• o1 (100) — order_date 2024-01-10, deliver_date 2024-01-20: open 10 days
• o2 (200) — order_date 2024-01-25, deliver_date 2024-02-05: open 11 days
• o3 (50), not delivered — order_date 2024-02-10, deliver_date NULL: still open
• o4 (80), delivered same day — order_date and deliver_date 2024-03-01: never open

The question: how many orders were open each month, and what were they worth?

Solution: a many_to_many relationship with match

Declare a many_to_many relationship between orders and dim_dates. match holds the "open on this day" test, and from_key and to_key name a unique column on each side.

Step 1: Define models

orders.model.aml

Model orders {
  type: 'table'
  table_name: 'ecommerce.orders'
  data_source_name: 'your_datasource_name'

  dimension id {
    type: 'text'
    primary_key: true
  }

  dimension order_date { type: 'date' }
  dimension deliver_date { type: 'date' }
  dimension amount { type: 'number' }

  measure open_orders {
    label: 'Open Orders'
    type: 'number'
    definition: @aql count(orders.id);;
  }

  measure open_amount {
    label: 'Open Amount'
    type: 'number'
    definition: @aql sum(orders.amount);;
  }
}

dim_dates.model.aml

Model dim_dates {
  type: 'table'
  table_name: 'ecommerce.dates'
  data_source_name: 'your_datasource_name'

  dimension date {
    type: 'date'
    primary_key: true
  }

  dimension month { type: 'text' }
}

Step 2: Define the relationship

order_open_date.relationship.aml

Relationship order_open_date {
  type: 'many_to_many'
  from_key: r(orders.id)
  to_key:   r(dim_dates.date)
  match: @aql
    // The order must be placed on or before the date
    orders.order_date <= dim_dates.date
      // And not yet delivered (or still open if deliver_date is null)
      and coalesce(orders.deliver_date, @9999-12-31) > dim_dates.date
  ;;
}

• > on deliver_date leaves the delivery day out. Use >= if an order should count as open on the day it is delivered.
• coalesce treats an order with no deliver_date as still open.
• many_to_many makes no claim about how many rows match, so Holistics protects every query against fan-out. That needs a row identity on each side, which is what from_key and to_key provide.

Step 3: Add it to a dataset

operations.dataset.aml

Dataset operations {
  data_source_name: 'your_datasource_name'

  models: [orders, dim_dates]

  relationships: [
    relationship(order_open_date, true)
  ]
}

Step 4: Query

explore {
  dimensions { month: dim_dates.month }
  measures { open_orders: count(orders.id), open_amount: sum(orders.amount) }
}

Holistics first resolves which order is open in which month, then joins the result back to orders on its key:

WITH resolved AS (
  SELECT DISTINCT orders.id, dim_dates.month
  FROM orders JOIN dim_dates ON <match>
)
SELECT resolved.month, COUNT(orders.id), SUM(orders.amount)
FROM orders LEFT JOIN resolved ON orders.id = resolved.id
GROUP BY 1

Result:

month	open_orders	open_amount	which orders
Jan	2	300	o1, o2
Feb	2	250	o2, o3
Mar	1	50	o3

• Each order counts in every month it was open at any point, and only once per month. o1 matched 10 days in January but counts once.
• The months add up to 5 against 4 orders. That is the intended answer: o2 was open in both January and February.
• o4 appears in no month, because it was never open.

The reverse direction works the same way. Count the days each order was open:

order	days open
o1	10
o2	11
o3	51

o3 is counted up to the last day in dim_dates.

"Open on the last day of the month" and "average open orders per day" are metrics built on top of this relationship. The relationship itself answers "open at any point in the month".

Joining to a period table instead

If the dimension stores periods rather than days, test whether the two ranges overlap. This suits fiscal periods, sprints or reporting cycles that have no daily table.

periods

label	start_date	end_date
Jan	2024-01-01	2024-01-31
Feb	2024-02-01	2024-02-29
Mar	2024-03-01	2024-03-31

Two ranges overlap unless one ends before the other starts. An order overlaps a period if it was placed on or before the period's last day, and delivered after the period's first day:

order_open_period.relationship.aml

Relationship order_open_period {
  type: 'many_to_many'
  from_key: r(orders.id)
  to_key:   r(periods.label)
  match: @aql
    orders.order_date <= periods.end_date
      and coalesce(orders.deliver_date, @9999-12-31) > periods.start_date
  ;;
}

Grouping by periods.label returns the same result as the daily version: Jan 2 orders (300), Feb 2 (250), Mar 1 (50). For example, o2 (25 Jan to 5 Feb) overlaps both Jan and Feb, while o4 (placed and delivered 1 Mar) fails > periods.start_date for Mar and matches no period.

The period table is much smaller than a daily one, so the join is cheaper. The trade-off is that you can only group by the periods it defines.

Interval overlap or validity windows?

Both patterns use a date inequality. The difference is how many dimension rows one fact row can match.

Each fact row matches...	Example	Pattern
at most one dimension row	an order and its price band	Validity windows (range)
any number of dimension rows	an order and every day it is open	Interval overlap (many_to_many)

Key points

• Both keys are required. from_key and to_key must each be one unique column. Either side can carry the measure, so both need a row identity.
• match must reference both models. A condition on one model only is a relationship filter. Write it as a where {} block, as in the polymorphic associations pattern.
• Decide the boundary. Choose whether the end date counts as open, and write > or >= to match.
• Period totals do not add up to the grand total. This is correct for overlap. Do not sum across periods to get a total.
• Open-ended events stop at the date table. An order with no end date matches every day up to the last row of dim_dates.
• Correctness costs a little performance. The query scans the measure side twice and builds a larger intermediate result than a plain join.

Additional resources

• Back to overview
• Validity windows pattern
• Relationships overview
• AML Relationship reference
• Fan-out issues