Apple: subscription state on a past date

Constraints

1. 1Only events on or before '2026-03-01' may influence the answer. The log runs past that date and those rows are the future as far as this question is concerned.
2. 2A subscription whose first event is after the date does not appear at all — sub8 starts on 2026-03-05.
3. 3State comes from the latest qualifying event: start or renew is 'active', cancel is 'cancelled', refund is 'refunded'.
4. 4payment_failed is 'grace' when the failure was 16 days ago or less, and 'lapsed' after that.
5. 5Two events on the same day are broken by event_id, which increases with time.
6. 6Order by subscription_id.

Worked example

sub3 failed payment on 2026-02-02 and renewed on 2026-03-08. On 1 March the failure was 27 days old, so the state was 'lapsed' — the renewal had not happened yet. sub2 failed on 2026-02-20, nine days before, so it was in 'grace'. Query the log without the date filter and sub3 reads as active, sub6 reads as cancelled, and sub8 appears with a state on a date it did not yet exist. Every one of those is a defensible answer to a question nobody asked: what is true now.

What this tests

Point-in-time reconstruction from an event log: filtering to the as-of date before picking the latest event, rather than after, and expressing a state machine whose outcome depends on how long ago the last event was.