# Interval overlap pattern

> Learn how to count a record in every period its date range overlaps, such as open orders per month or active headcount per month

## 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

<ReactFlowDiagram id="erd-interval-overlap" height={240} fixed />

**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.

<ReactFlowDiagram id="interval-overlap-open-orders" height={360} fixed />

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

```aml title="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);;
  }
}
```

```aml title="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

```aml title="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

```aml title="operations.dataset.aml"
Dataset operations {
  data_source_name: 'your_datasource_name'

  models: [orders, dim_dates]

  relationships: [
    relationship(order_open_date, true)
  ]
}
```

### Step 4: Query

```aml
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:

```sql
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:

```aml title="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](/docs/modeling/modeling-patterns/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](/docs/modeling/modeling-patterns/polymorphic-associations).
- **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](/docs/modeling-patterns)
- [Validity windows pattern](/docs/modeling/modeling-patterns/validity-windows)
- [Relationships overview](/docs/relationships)
- [AML Relationship reference](/reference/aml/relationship)
- [Fan-out issues](/docs/joins/fan-out-resolution)