customer_dim(customer_id, valid_from, valid_to, segment) and orders(order_id, customer_id, ordered_at, revenue)

customer_dim is a slowly changing dimension: each row holds the segment a customer belonged to between valid_from and valid_to, where valid_to is exclusive and NULL means the row is current.

Attach the segment that was valid at the moment of each order and return 2026 revenue by historical segment.

Return segment and revenue, ordered by segment.

Joining on the current row only, or on valid_to >= ordered_at, both reattribute history: an order placed while the customer was 'smb' must stay with 'smb' even after they are upgraded.

Tables

customer_dim

customer_id  valid_from           valid_to             segment
-----------  -------------------  -------------------  ----------
1            2026-01-01 00:00:00  2026-06-01 00:00:00  smb
1            2026-06-01 00:00:00  NULL                 enterprise
2            2026-01-01 00:00:00  NULL                 smb

orders

order_id  customer_id  ordered_at           revenue
--------  -----------  -------------------  -------
1         1            2026-03-01 10:00:00  100.00
2         1            2026-06-01 00:00:00  200.00
3         1            2026-08-01 10:00:00  300.00
4         2            2026-04-01 10:00:00  50.00
5         1            2025-12-31 10:00:00  999.00

Expected result

segment     revenue
----------  -------
enterprise  500.00
smb         150.00