Agregar dados SCD Tipo 2 "a partir de" cada dia

Problema

Ao trabalhar com dados SCD tipo 2, é fácil ver o estado de uma tabela "a partir de" um determinado momento usando suas colunas de data, por exemplo: valid_frome valid_to. Por exemplo:

select * from table
where '2023-11-01' between valid_from and valid_to

Você pode então agrupar ou usar funções de janela para agregar.

Mas e se eu quiser fazer isso repetidamente, para cada data de um intervalo (por exemplo: diariamente)? Não preciso agregar essas datas, apenas dentro de cada uma.

Exemplo

Digamos que eu tenha uma tabela que rastreia o quantitynúmero de pessoas de cada um reservation_id. Tanto a quantitypersonalidade das pessoas quanto a reservation_statuspodem mudar com o tempo, conforme monitorado pela tabela. Cada reserva está anexada a um arquivo event_id. event_dateestá incluído para ajudar a limitar o escopo, se necessário (veja as suposições abaixo).

PostgreSQL já que o BigQuery é mais difícil de testar – db-fiddle /SQL:

CREATE TABLE Reservations (
  "reservation_id" INTEGER,
  "reservation_status" VARCHAR(9),
  "quantity" INTEGER,
  "event_id" INTEGER,
  "event_date" DATE,
  "valid_from" DATE,
  "valid_to" DATE
);

INSERT INTO Reservations
  ("reservation_id", "reservation_status", "quantity", "event_id", "event_date", "valid_from", "valid_to")
VALUES
  ('1', 'active', '4', '100', '2023-05-25', '2023-01-01', '2023-01-02'),
  ('2', 'active', '2', '200', '2024-01-07', '2023-01-01', '2023-01-03'),
  ('3', 'active', '7', '100', '2023-05-25', '2023-01-02', '9999-12-31'),
  ('4', 'active', '1', '200', '2024-01-07', '2023-01-02', '9999-12-31'),
  ('1', 'active', '5', '100', '2023-05-25', '2023-01-03', '9999-12-31'),
  ('5', 'active', '8', '100', '2023-05-25', '2023-01-03', '9999-12-31'),
  ('2', 'cancelled', '2', '200', '2024-01-07', '2023-01-04', '9999-12-31'),
  ('6', 'active', '3', '100', '2023-05-25', '2023-01-06', '9999-12-31');

Embora isso seja, em última análise, para o BigQuery, a resposta será aceita em qualquer dialeto, desde que seja um tanto genérica.

Premissas

• As datas "a partir de" podem ser uma lista ou intervalo baseado em valid_frommínimo/máximo
• valid_tode 9999-12-31são os dados mais recentes
• Todas as reservas para um determinado evento serão entre event_date - INTERVAL '2 years'e event_date. Isso não muda nada neste exemplo, mas talvez seja útil para dimensionar (?)

Saída desejada

Gostaria de saber a soma dos quantityagrupados por event_ide reservation_status a partir de cada intervalo (dia).

^{Estimativa aproximada dos valores das linhas. Será diferente se usar o intervalo completo de datas.}

Eu essencialmente quero fazer o seguinte:

/* Invalid SQL, just for conceptual purposes */

-- Given a list of dates, for each "date":
select
  event_id,
  reservation_status,
  sum(quantity)
from table
where {{date}} between valid_from and valid_to
group by
  event_id,
  reservation_status

Acredito que isso pode ser feito usando linguagem processual, como um loop for, mas sinto que estou pensando demais nisso e tendo problemas para combinar conceitos mais simples.