medium · ~12 min
Product analytics wants to attribute each user action in events to the subscription in subscriptions that was active on the day the event occurred.
events
subscriptions
user_id
event_date
start_date
end_date
event_id
event_type
sub_id
plan
Input contract: sub_id is unique and non-null in subscriptions; event_id is unique and non-null in events; all columns are non-null. A single user can hold multiple non-overlapping subscription periods over time (start_date <= end_date) and generate multiple events across different dates.
start_date <= end_date