Interval overlap pattern

What is interval overlap?Copy Anchor Link

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

Sample data:

orders

dim_dates (one row per day, 91 rows)

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.

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

Solution: a many_to_many relationship with matchCopy Anchor Link

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

Step 1: Define modelsCopy Anchor Link

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);;
  }
}

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 relationshipCopy Anchor Link

Relationship order_open_date {
  type: 'many_to_many'
  from: r(orders.id)
  to: 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 AMQL protects every query against fan-out. That needs a row identity on each side, which is what from and to provide.

Step 3: Add it to a datasetCopy Anchor Link

Dataset operations {
  data_source_name: 'your_datasource_name'

  models: [orders, dim_dates]

  relationships: [
    relationship(order_open_date, true)
  ]
}

Step 4: QueryCopy Anchor Link

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

AMQL 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:

• 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:

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 insteadCopy Anchor Link

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

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:

Relationship order_open_period {
  type: 'many_to_many'
  from: r(orders.id)
  to: 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?Copy Anchor Link

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

Key pointsCopy Anchor Link

• Both keys are required. from and to 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 resourcesCopy Anchor Link

• Back to overview
• Validity windows pattern
• How joins work
• AML Relationship reference
• Fan-out issues